Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a set of data and need to do the rolling avg on the last three items. It is not a date range but the name of the month.
If([2019 Source.Size]='4mm',Avg([Actual Cost £]))
and my grouping is by Month but as above it is only the name of the month and not a date.
Hello,
Thank you very much for sharing this information. I assume that you have a Date field, from which you extract the Months and Years. If you don't have one then I would recommend creating one within the Data load editor, such as:
Date:
Load *,
MakeDate(Year, Match(Month, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'), 1) as Date
Resident Data;
This will allow you to order based on date instead of the Month string name.
In any case, there is an tutorial video on how to calculate the Rolling Average, also known as Moving Average [3]. I have tried the suggestions in the video and I have the following output:
I hope that this information was helpful.
---
[3] https://www.youtube.com/watch?v=Q1FkCSOHvno
Can you please provide a data sample?
For example you can share an image of how the data is displayed on your side (fake or demo data) and then mark which records you would like to count within your expression. Pointing also the expected outcome based on the provided data sample, will help other community members to understand better the use case scenario.
So the information that is needed:
Hello,
Thank you very much for sharing this information. I assume that you have a Date field, from which you extract the Months and Years. If you don't have one then I would recommend creating one within the Data load editor, such as:
Date:
Load *,
MakeDate(Year, Match(Month, 'January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December'), 1) as Date
Resident Data;
This will allow you to order based on date instead of the Month string name.
In any case, there is an tutorial video on how to calculate the Rolling Average, also known as Moving Average [3]. I have tried the suggestions in the video and I have the following output:
I hope that this information was helpful.
---
[3] https://www.youtube.com/watch?v=Q1FkCSOHvno