Summing X months before given month

[Saryk]

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.

[vunguyenq89 · Accepted solution]

Hi,

You can use =RangeSum(Above(Sales,0,$(X))) with X being the number of months you want to sum. Make sure to sort the chart by month dimension.

There is a nice example for RangeSum(Above(...)) on the help site at https://help.qlik.com/en-US/sense/November2019/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/Range...

Hope this helps!

BR,

Vu Nguyen

[Saryk]

That is an elegant solution, but what if I have many entries per month ? 

I have to check what my data looks like, I'll see if I can make that can work. 

[vunguyenq89]

Hi, 

If you have many entries per month, the expression should be =RangeSum(Above(Sum(Sales),0,$(X)))

BR,

Vu Nguyen

[Saryk]

This doesn't work when you apply filter though. If I want an average of the data over the week X, X-1 and X-2, I'd have 

RangeAvg(Above(Sum(data), 0, 3))

and then order by week.

Thing is, as soon as I select a week for instance, that value changes since there are no other weeks to average from.

I'd like this value to not change ; if I have this data (blue is loaded and orange is calculated in graph)

Week	Data	Last 3 weeks average
2	4	4
3	5	4,5
4	3	4
5	7	5

 
and I select week 5, the average will jump from 5 to 7.

Should I make a data table and create the graph from there ?

Should I insert the formula in the data loading ?

[vunguyenq89 · Accepted solution]

Then you can add Set Analysis to clear all selection in Week  before calculating the expression. For example

=RangeAvg(Above(Sum({<Week=>}Data), 0, 3))

With this Set Analysis, the expression will not response to selections in Week, but still responses to selections in other fields.

[Saryk]

Yes, this works perfectly fine !

Thank you 🙂

[Saryk]

Since I have multiple dimensions I can switch between, what would the grammar be to exclude the filtering of "Weeks", "Months" and "Years" ?

[vunguyenq89 · Accepted solution]

Hi, it would be

=RangeAvg(Above(Sum({<Week=,Month=,Year=>}Data), 0, 3))

[Saryk]

Amazing, thanks again !