Year over year cumulative average

mberkeley · 2022-09-28T16:49:59+00:00

There was an error rendering this rich post.

mberkeley

I am trying to create a line chart with the year as a series. The data points are per month with a cumulative average.

Data:

Year Month Closed Cases Total Cases % Cumulative % (Cum. Math)

2022 1 50 100 50% 50% (50/100)

2022 2 100 150 75% 60% (150/250)

2023 3 100 100 100% 71.43% (250/350)

etc. across multiple years where each year resets the starting point at month 1.

I then would create a line graph ordered by Month plotting the cumulative % with the year as a series.

I am having trouble calculating the cumulative average for each year. When Group the Y/M data the calculation is only based on the given Y/M, not Y + all months so far that year.

thanks in advance for any assistance.

Find more posts tagged with

Magic ETL

Accepted answers

GrantSmith

You can use a window function to keep a running total and then divide them

SUM(SUM(`Cases Closed`)) OVER (ORDER BY `Year`, `Month`)
/
SUM(SUM(`Total Cases`)) OVER (ORDER BY `Year`, `Month`)

All comments

GrantSmith

You can use a window function to keep a running total and then divide them

SUM(SUM(`Cases Closed`)) OVER (ORDER BY `Year`, `Month`)
/
SUM(SUM(`Total Cases`)) OVER (ORDER BY `Year`, `Month`)

mberkeley

I need to spend more time looking at the Window function to see if understand how to do this. Thanks for giving me direction.

mberkeley

Thanks, Grant. The solution worked.