Rolling average using windowAvg at the edge of the range of filtered data

Question & Answer
, , ,
1

I’m trying to make a rolling average that uses data beyond the edge of a date filter, so it doesn’t converge to the edge value.

To do this, I’ve tried to use windowAvg to make a calculated field to create a 7 day rolling average (into the past) shown on date-filtered stacked bar combo chart.

windowAvg(
  sum(sales),
  [date DESC],
  0,
  6
)

image
image831×637 65.7 KB

But the far left average point on January 17th should not be equal the data point shown on the bar. Instead, it should be below it, as can be seen in the left hand visual at Rolling average comparison

How can I properly “truncate” a windowAvg at the edge of the range shown in a filtered visual?

3 Likes
2

Hi @michalc! Welcome to the QuickSight Community! :slight_smile:

Thanks for posting your question. I have pinged the team and you should hear back tomorrow before end-of-day US. Thanks!

1 Like
3

Hello @michalc, thank you for taking the time to set this up in arena, that really helps to demonstrate the issue you are facing. The problem with this windowAvg function is that once you filter the date field on the visual, the first value that the calculation has to reference is January 17th. I’ve spent some time testing this, I tried periodToDateAvg, periodToDateAvgOverTime, and runningSum but none of these functions allow for a calculation level to be added like what is allowed in a sumOver or avgOver function that will let the aggregation run before the filter is applied to the data. Due to this, I am thinking that you will likely want to add this calculation into your custom SQL when you ingest the data, because I am unable to build a work-around with the calculated fields.

Are you building your dataset with custom SQL already and if so, what database are you querying? With a little more information, I can try to guide you to a solution using SQL.

2 Likes
4

Hi @DylanM,

Thanks for your reply.

Understood that this might have to be done with custom SQL - we’re querying a PostgreSQL database so that should be possible. One small complication is that the data is aggregated in QuickSight - so in terms of the example here, each bar shows the sum of all the sales for that day. To do the rolling average over the sum in the SQL, it might have to be that we do that summation in the SQL itself?

Thanks,

Michal

5

Hello @michalc, yes, I think you are on the right track. If you are going to calculate the running sum in the SQL query, then I think it would be best to change the daily sum aggregation to SQL as well.

This Medium article has some information about building a running sum in PostgreSQL that may be helpful!

6

Ah - I don’t have a cumulative/running sum in this particular case, instead:

  • It’s a “regular” sum over each day - so sum(...) .... GROUP BY date sort of thing
  • Then a rolling 7 day average on that sum
1 Like
7

Hello @michalc, after applying that change, did it end up providing you with the solution you were looking for or did you end up running into another issue on the data returned in the visual?

1 Like
8

Ah so didn’t end up doing this. To do it in the SQL would result in us have to have another dataset behind the dashboard because we aggregate in different ways, and so I don’t think it’s worth the complexity for us in this particular case - the rolling average is a visual nice to have I think.

No other issue though - happy to know the solution if/when we need it.

1 Like
9

Hey all, I am having the same issue. It seems like the solution proposed here was just to calculate it in SQL. This seems like a big gap because otherwise the windowAvg function would always create a tail on a graph or table where the edge of the data is innacurate for N number of days depending on your window size. (3 days, 7 days, 30 days, etc.). Is there a solve where the calculated field has access to “more” data than is available in the visualization?