Magic ETL
Magic ETL
Can I dynamically group data to two periods (Current 12 month and Prior 12 months) ?
Can I dynamically group data to two periods (Current 12 month and Prior 12 months) using window function so I can select time periods to compare. Client wants to be able to select months in the past and compare rolling 12 month as of the last date select date.
I tried this formula for Current period, but it doesn't aggregate all the way.
case when dense_rank() over (order by `Posting Period` desc) <=12 then sum(`Payment Amount`*-1) end
When I remove Posting Period column from the table, the Current Period Payments column doesn't aggregate and remains the same. i want to be able to add Prior 12 months data Next to Current.
Answers
-
I would suggest you look at these two posts on creating these if you are wanting to display as table cards:
https://dojo.domo.com/discussion/52681/domo-ideas-conference-beast-modes-running-totals#latest
https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest
If you can display this information as a bar+line graph, I would suggest you use the period over period cards and select last 24 months in your date range filter and group by month and compare to 12 months prior. This would not require any beast mode work and would let you see how a month compared to the same month in the prior year.
**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 -
Thank you, Mark. I will try rolling summaries. I do need it in the table cards.
0 -
I’d recommend using a date dimension with custom defined offsets. Using the custom date offset dimension you can have the current value and the value from 12 months ago. This will simplify your graphing and correctly calculate the current month to the partial month from the previous period . I’ve done a Previous write up here: https://dojo.domo.com/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons#latest
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Thank you for reminding about this, Grant! And, if they want to choose 12 months to compare to the previous time period, they could do it by using Date page filter or calendar filter card, right?
0 -
Yeah, it'll automatically filter based on the date or card filters, the period offset won't be able to change but the timeframe they're looking at can. You can always define additional offsets if necessary.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Categories
- All Categories
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 114 SQL DataFlows
- 654 Datasets
- 2.2K Magic ETL
- 4.1K Visualize
- 2.5K Charting
- 803 Beast Mode
- 79 App Studio
- 44 Variables
- 759 Automate
- 189 Apps
- 480 APIs & Domo Developer
- 73 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 408 Distribute
- 119 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive