3 month moving with current month for a date column
I am trying to calculate a SQL formula for a moving 90 day period based on month. For example what I would like to see now is the date column rolled up into DEC,JAN,FEB and as we move to next month I would expect to see JAN,FEB,MAR.
I know the current month function to get only the current month moving but having trouble integrating the following two months as well with it.
Let me know your thoughts...
Thanks
Answers
-
@MichelleH @MarkSnodgrass How would I go about this in a window function under the rank and window tile? or do you know what the beastmode would look like?
0 -
@gbrown Here is the knowledge base article on how to configure the Rank & Window tile in ETL: https://domo-support.domo.com/s/article/360044876094?language=en_US
For your situation, you would want to use a framed average or sum, ordered by Date. To calculate the 3-month average you would set Preceding = 0 (start with current month) and Following = 2 (calculate through two months from now).
0 -
@MichelleH Is there a way to do a filter formula to calculate without the rank and window tile. it can be in a beastmode if needed. Something similar to
CASE WHEN YEAR(`Date`) >= YEAR(CURDATE()) AND MONTH(`Date`) <= ADDDATE(CURDATE(), INTERVAL 3 MONTH)
THEN 'SHOW'
ELSE 'HIDE'
END
The one problem is that it is picking up end months for 2023 and start for 2024 ( I am not sure why) but it seems like it could work maybe my logic needs help.
Thanks
0 -
@gbrown if you are only wanting to show the current month and the next two months and wanting to show the average of those values, I think you could keep it pretty simple by using the last_day function and the date_add function like this:
CASE when LAST_DAY(`dt`) >= LAST_DAY(CURRENT_DATE()) AND LAST_DAY(`dt`) <= LAST_DAY(DATE_ADD(CURRENT_DATE(), INTERVAL 3 month)) THEN 'Inside 90-day window' ELSE 'Outside 90-day window' END
You would then add this to your filter and filter to "Inside 90-day window" and then drag your value field into your table and choose Average for the aggregation type.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive