Understanding WindowAvg to calculate variable rolling average

Question & Answer
, , , ,
1

Hi.

I’m trying to create a line chart with two series:

1- the average rating for the date
2- the rolling average for the previous X number of days

Here is what the chart looks like:

image
image868×454 38.6 KB

and here are the calculations involved:

// Average Overall Rating
avgIf({RATING_APP} , {RATING_APP}>0)

// Rolling Overall Average
windowAvg(
    avgIf({RATING_APP},{RATING_APP}>0),
    [{RATING_DATE} ASC],
    0,${pRatingWindowInDays}
    )

as you can see, if I decrease the slider value, the rolling average line will hug the daily average line, and If the slider is increased the rolling average gets smoothed out.

image
image876×463 33.5 KB

image
image873×456 36 KB

Here I don’t understand why when the slider is at 7, the first value of the rolling average (March 1st, 2022) is at 4.5 if the dataset does not have any records from before March 1st, 2022.

Shouldn’t the rolling average’s first value always equal the first Avg() value regardless?

If it makes any difference, the time dimension (RATING_DATE) is a datetime field with complete time component

2

Hello @Ali_B !

I’m curious if this is happening because you have your calculation sort set to ASC. Could you try setting it to sort by DESC and see if that gives you the expected value?

1 Like