Is it possible to indirectly calculate a Rolling Maximum in Magic 2.0?
I know the in-built "Rank and Window" tile in Magic does not allow for Max/Min window functions -- is there a work-around that anyone has found, however clunky?
Raw data essentially is a variation of:
1/1/2021 | $100
2/1/2021 | $200
3/1/2021 | $180
4/1/2021 | $190
5/1/2021 | $220
Appreciate the help, thank you!
Best Answer
-
Thinking about this a bit more - you could utilize a Dataset View and a calculated field with a window function to calculate the rolling max and then use that as an input into your Magic 2.0 Dataflow.
Just make sure your partitioning correctly based on your assets.
MAX(`random_number`) OVER (ORDER BY `dt`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0
Answers
-
Hi @Ritwik
You can use the LAG function in the rank and window tile to calculate the different columns/values for the past X rows (depending on how long your window is). Assuming you have Magic ETL 2.0 you can then utilize an Add Formula Tile to calculate the GREATEST value of all of your lag columns. Not pretty but that should get you the rolling max for you. ETL 1.0 I wouldn't even try, there isn't a simple way.
GREATEST(`value`, `value_lag1`, `value_lag2`, `value_lag3`...)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
Hey @GrantSmith
Thanks for responding! That makes sense, but wouldn't necessarily work for us -- we have a row per day per asset (and the dataset grows). Guess I'll need to do in a SQL dataflow and join based on date back
0 -
Thinking about this a bit more - you could utilize a Dataset View and a calculated field with a window function to calculate the rolling max and then use that as an input into your Magic 2.0 Dataflow.
Just make sure your partitioning correctly based on your assets.
MAX(`random_number`) OVER (ORDER BY `dt`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
@Ritwik you can implement a
max( max(amount) over ( order by date asc ) in a dataset view and then use that as an input in Magic 2.0.
You could even create that max_view and then JOIN raw_data in a second DSV for maximum performance. Don't do it in a SQL etl -- waste of time.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
My first time using DSV (I know I should be using it much more...)! Great idea, thank you both @GrantSmith @jaeW_at_Onyx
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 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
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 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