Moving average for small period

Question & Answer
, ,
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!!