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

Ritwik
Ritwik Contributor
in Magic ETL

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 Coach
    edited September 2021 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`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    edited September 2021

    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!**
  • Ritwik
    Ritwik Contributor

    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

  • GrantSmith
    GrantSmith Coach
    edited September 2021 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`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    @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"
  • Ritwik
    Ritwik Contributor

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

Categories