Get Free Trial

Rolling average over a week

Chris Anderson asked on October 21, 2020

Hi there,
I’ve read on the forum that there is no way to group data by week (as we can using months, qtr and year) using the existing GUI.
Are there any plans to introduce this feature? If not, do you offer any workarounds in the interim that would allow me to calculate a rolling average per week (7 day period)? Bearing in mind that the current Average logic works based on lines / transactions and I’m after something to do this over 7 calendar days regardless of whether an entry was made for a given day.

I can currently create a 7 day average by just using the last 7 days worth of data (today – 7) but this changes each day rather than calculating the average per week.
Is there any logic I could add to a calculated field to check for dates and work from there?


1 answer

Illia Yatsyshyn Illia Yatsyshyn Flexmonster October 22, 2020

Hello, Chris,
Thank you for contacting us.
We agree that it would be useful to have the possibility to group the data by weeks. However, our roadmap is full at the moment.
Therefore, we suggest considering the following workaround:
Preprocess your data set before passing it to Flexmonster. The resulting data set should contain an additional field that would represent a week. Next, use this field to group the data by weeks on the pivot.
Please see an example we have prepared to demonstrate the described approach.
Before feeding the data to Flexmonster, it is passed through the preprocessData function. In its turn, the preprocessData function serves to complement the existing data set with a field named “Week” and calculating a corresponding member name for each record.
Please let us know if it works for your case.
Feel free to contact us in case further assistance is needed.
Kind regards,

Please login or Register to Submit Answer