Rolling Avg on the last three items

[RichardLee]

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.

[Andrei_Cusnir]

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:

1. Image of sample data (fake or demo, no need to include original data with sensitive information). Perhaps put the data in a table chart and share the screenshot.
2. Note which records you are expecting to count (e.g. Last two line is expected to be counted)
3. The expected outcome from the expression (e.g. With the provided data, we should see the number "100" as result etc.)

[RichardLee]

[Andrei_Cusnir · Accepted solution]

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.

---

[1] https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

[2] https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTim...

[3] https://www.youtube.com/watch?v=Q1FkCSOHvno