Running Total Year over Year

user06209 · 2021-03-11T17:15:42+00:00

There was an error rendering this rich post.

Hello,

Is there a way to do a running total year over year inside analyzer or do I have to go into ETL and manipulate the data in order to have a year over year?

When using the running total graph, I tried to change my X-Axis by month and Series by year but it made my current year the same value for future months but my previous year was correct.

Find more posts tagged with

Accepted answers

All comments

GrantSmith

Hi @user06209

You can utilize window functions in a beast mode (you just need to have them enabled - talk to your CSM)

SUM(SUM(`Amount`)) OVER (PARTITION BY YEAR(`Date`) ORDER BY `Date`)

Derreck

@GrantSmith Can this same beast mode be utilized within a Formula Tile in ETL 2.0?

GrantSmith

@Derreck - No. Window functions aren't supported in the formula tile in ETL 2.0. You can use the Rank and Window tile though to do the same thing.

jaeW_at_Onyx

you can do a cumulative sum using Rank & Window

SUM(SUM(`Amount`)) OVER (PARTITION BY YEAR(`Date`) ORDER BY `Date`)

but at the moment you cannot do a Subtotal in Rank & Window. To do this equivalent you'd do a GROUP BY and then JOIN the data back to the original.

SUM(SUM(`Amount`)) OVER (PARTITION BY YEAR(`Date`) )