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`)
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`...)
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 -
@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.
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
- 10.6K All Categories
- APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 337 Workbench
- 250 Domo Best Practices
- 11 Domo Certification
- 460 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 83 Dojo
- Domopalooza
- 1.1K ζ₯ζ¬ζ―ι¨
- 4 ιε ΄-ζ₯ζ¬ζ―ι¨γΈγγγγ
- 22 γη₯γγ
- 62 Kowaza
- 295 δ»²ιγ«ηΈθ«
- 649 γ²γγγε ±ζ