Calculate 4 week averages over previous 4 weeks

Question & Answer
, ,
1

Hi team,

I’m trying to replace my Excel sheets with QuickSight and I’m running into an issue while calculating averages.

My data set contains two columns, a “week” and a “users” column. I want to add two calculated fields:

  1. The average of the past 4 weeks
  2. The average of the 4 weeks prior to that

So for instance, for the week “May 18 2024” the average of the past 4 weeks should be (10+18+9+15)/4 = 13 and the average of the 4 weeks prior to that should be (4+9+10+8)/4 = 7.75.

I’ve created the first calculated field with the formula windowAvg(sum({Users}), [{Week} DESC], 0,3) and that works perfectly, but I can’t get the second calculated field to work. I’ve tried windowAvg(sum({Users}), [{Week} DESC], 3,7) but that does not give the answer I expect. Any thoughts on this one?

I’ve created a dashboard with a sample data set in case that helps explain things

1 Like
3

Hi @lstigter ,

welcome to the community :tada:

Before I come to the solution I will mention two points.

  1. windowAvg is not the solution,. With start and end you can determine the rows that will be included in the calculation. Start means the rows above your original row and end the rows below. It it not possible to use it the way you wanted. See also: windowAvg - Amazon QuickSight
  2. windowAvg(sum({Users}), [{Week} DESC], 4,7) => It is week t-4 till week t-7 instead of t-3 till week t-7, right?

Now lets have a look into a solution:

The first windowAVG would work. An alternative to calc week t-0 to t-3 is:

(
    sum(Users) +
    lag(sum(Users), [Week ASC], 1) +
    lag(sum(Users), [Week ASC], 2) +
    lag(sum(Users), [Week ASC], 3)
) / 4

Lag function calculates the value of a prev. row. Here we sum up the acutal row (t0) and the three previous ones.

To calculate the 4 week before (week t-4 to t-7) you can use:

(
    lag(sum(Users), [Week ASC], 4) +
    lag(sum(Users), [Week ASC], 5) +
    lag(sum(Users), [Week ASC], 6) +
    lag(sum(Users), [Week ASC], 7)
) / 4

or much shorter:

lag({week t-0 to t-3}, [Week ASC], 4)

You can find the calculations here:
Calculate 4 week averages over previous 4 weeks

Best regards,
Nico

3 Likes
4

Thanks, @Nico, for this fantastic reply! :slight_smile:

1 Like