topic Re: Summing X months before given month in Visualization and Usability

Summing X months before given month

Saryk — Wed, 20 Nov 2019 11:11:00 GMT

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.

Re: Summing X months before given month

vunguyenq89 — Wed, 20 Nov 2019 12:29:49 GMT

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/RangeFunctions/rangesum.htm

Hope this helps!

BR,

Vu Nguyen

Re: Summing X months before given month

Saryk — Wed, 20 Nov 2019 12:58:51 GMT

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.

Re: Summing X months before given month

vunguyenq89 — Wed, 20 Nov 2019 14:55:44 GMT

Hi,

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

BR,

Vu Nguyen

Re: Summing X months before given month

Saryk — Fri, 22 Nov 2019 10:37:18 GMT

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 ?

Re: Summing X months before given month

vunguyenq89 — Fri, 22 Nov 2019 14:00:19 GMT

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.

Re: Summing X months before given month

Saryk — Mon, 25 Nov 2019 12:10:42 GMT

Yes, this works perfectly fine !

Thank you 🙂

Re: Summing X months before given month

Saryk — Mon, 25 Nov 2019 12:12:58 GMT

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

Re: Summing X months before given month

vunguyenq89 — Mon, 25 Nov 2019 12:27:01 GMT

Hi, it would be

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

Re: Summing X months before given month

Saryk — Mon, 25 Nov 2019 12:30:05 GMT

Amazing, thanks again !

Re: Summing X months before given month

drorelkouby — Mon, 17 Jul 2023 17:21:22 GMT

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?

Thanks

Dror

Re: Summing X months before given month

drorelkouby — Tue, 18 Jul 2023 18:31:07 GMT

I found the answer to my question using this amazing post:

https://community.qlik.com/t5/Design/Accumulative-Sums/ba-p/1468010