Is it possible to indirectly calculate a Rolling Maximum in Magic 2.0?

Ritwik
Ritwik Atlanta 🟑

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

  • GrantSmith
    GrantSmith Indiana πŸ”΄
    edited September 20 Accepted Answer

    @Ritwik

    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`)
    

Answers

  • GrantSmith
    GrantSmith Indiana πŸ”΄
    edited September 20

    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`...)
    
  • Ritwik
    Ritwik Atlanta 🟑

    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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟀

    @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.

  • Ritwik
    Ritwik Atlanta 🟑

    My first time using DSV (I know I should be using it much more...)! Great idea, thank you both @GrantSmith @jaeW_at_Onyx