Year over year cumulative average

mberkeley
mberkeley Contributor
in Magic ETL

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.

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

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

    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
    mberkeley Contributor

    Thanks, Grant. The solution worked.

Categories