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

Ritwik

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!

GrantSmith

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

GrantSmith

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

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

@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

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