Moving average for small period

Q&A
, ,
1

Hi,

I’m struggling with showing 30-days moving average for a smaller period of time in a table.
Like I have table with date, company and amount and I want to calculate 30-days moving average and show only 7 days period.
But when I’m using date filter and show last 7 days, obviously, moving average is calculated only for chosen days.
I used a calculated field, described here and mine looks like this:

ifelse(
    max({days_from_now}) <= ${daysBefore},
    windowAvg(
	sum({amount}), 
        [truncDate("DD",{date}) ASC],
        30,
        0,
	[{company}]
	),
    NULL
)

where days_from_now is a calculated field and ${daysBefore} is a parameter to chose how many days to show.
After that, I can create filter and exclude NULL from visual.
This approach actually works, but is there more straightforward way to calculate 30-days moving average and show only chosen dates?

Thank you!

1 Like
2

Hello @Valentin, hope this message finds you well!

I have done some similar once, so my suggestions for your problem is:
To calculate a 30-day moving average and display only a 7-day period, you should first, create a calculated field for the 30-day moving average without applying any date filter. This ensures that the moving average is calculated correctly for all dates, even those outside the 7-day range you want to display. You can use a function like:
windowAvg(sum({amount}), [truncDate("DD", {date}) ASC], 30, 0, [{company}])
This ensures the calculate the moving average.
Then, after calculating the moving average for the entire dataset, apply a date filter in the visualization to display only the last 7 days.
The principal point is that this filter does not affect the moving average calculation.
Finally, make sure the visualization is set to exclude null values, if they exist.
This approach ensures that the 30-day moving average is calculated correctly for each day, allowing you to filter the visualization to show only the desired 7-day period.
Please, tell me if this work for you!!

3

Valentin, lary_andr,
I have similar requirement to show 30-day moving average of count of records. I have calculated field windowAvg(count({id}), [{bill_date} ASC],30,0,[{company}]).
I have filter controls for both bill_date and company, so users can filter for the desired date range and company. So, my requirement is not to show fixed number of values on the line chart, but all the days that user selects. It could be more than 30 or less than 30.

@Valentin, can you please let me know how you achieved this using days_from_now? What is the calculated expression for the calculated field days_from_now? Is it the difference of current date and the date field?

@lary_andr, I did not understand what you mean by “after calculating the moving average for entire dataset, apply a date filter in the visualization”. Can you please elaborate? In my case, the date filter is impacting the moving average calculation. If user filters for just one day, the total count of records for that day is same as moving average value, which is incorrect.

Appreciate your help.

4

Hi, kborra

Sorry for the late answer.
In my case the calculated field for “days_from_now” looks like this:

dateDiff(truncDate("DD", {settled_at}), truncDate("DD", now()), "DD")

Yes, that’s the difference in days between current date and date field, so, basically I in first place find the dates to calculate MA-30 and calculate it for them, and then exclude all other values.

But now I struggle with slowness of my approach, because quicksight takes too long to calculate 30-day moving average for every day for every company, so any tips would be appreciated.

P.S. @lary_andr, your approach also doesn’t work for me.

Thank you!

5

@Valentin
I opened a separate topic for my issue and am happy to say that I was able to resolve my issue using the suggestion given in Moving average. Thank you.

2 Likes