I have a line chart on Excel that represents the sum of all sales of months (X-2) + (X-1) + (X) for every month X.
So dimension is months, and for April the line would show the sum of February, March and April ; and for May it would sum March, April and May.
I can't wrap my head around a formula that would work on Qlik Sense, and I would appreciate a bit of help 🙂
I imagine the pseudocode would be "Sum(sales) if (month >= month - 2 and month <= month)", but I can't make the difference between the month dimension and the month value.
I like the idea of using the Above function because I can let the user to choose the offset and the count using Input Variables (comparing to "As-Of" table), but is there a way to calculate the rolling X months but yet to display in the table chart only a specific month?
for example, if my data is:
Week
Data
Last 3 weeks average
2
4
4
3
5
4,5
4
3
4
5
7
5
and now I want to select Week5, i still want to see:
Week
Data
Last 3 weeks average
5
7
5
I can only assume that in this case Above() won't work, but is there other way to achieve it and still have it dynamic for the user to place the offset and the count for the rolling average?
