Rolling sum on Qlik Sense

[lzanetti]

Hi all,

I need to build a "rolling sum" plot (or table as well), i. e. a sum that month by month runs on the latest 12 months.

More precisely, I have a graph that shows a variable (the number of sold products) month by month.

The other variable I want to show is the sum of sold products on one year up to that month.

The result should be a plot showing (month by month) the units sold in that month (e.g. March 2017) and the units sold on one year that far (e.g. April 2016 - March 2017).

I hope the request is clear.

Any help would be very appreciated.

Thanks, LuigiZ

[OmarBenSalem]

check the attached app:

[lzanetti]

Hi Omar,

I understand the editing you made to the formul and it makes sense, but if you plot its result aside of a simple monthly sum, you will see that it's not correct (there are a lot of zeros, then on the last 12 posistions a value tht equals the monthly total).

I attach your app with this visualisation. and removed some items on the dashboard.

[agigliotti]

Hi Luigi,

Try with this expression below:

Sum(

Aggr(

RangeSum(

    Above( total  Sum( {< [PostingDate.autoCalendar.YearMonth] = >} Quantity)

        , 0,3 )

        )

,[PostingDate.autoCalendar.YearMonth] )

)

However I suggest you to take a look at Calculating rolling n-period totals, averages or other aggregations

[lzanetti · Accepted solution]

Tank you agigliotti!

I've read that article, as it was advised earlier by omarbensalem‌, but couldn't transpose it in my case.

I did some try as well, but with no result.

The formula you suggest gives a total, but it's not the right one (it makes a sum but I don't understand the calculus).

So I tried to remove the outest "Sum( Aggr(" and it works fine    (see attached file).

So the right formula, for my case, is:

RangeSum(

               Above( total Sum( {<[PostingDate.autoCalendar.YearMonth] = >} Quantity)

        ,           0, 3)

                    )

This makes a 3 months Rolling sum.

By wrinting 12 instead of 3 I make 12 months rolling sum.

Great thank you to all who contributed.

[agigliotti]

just to know the expression i gave you should be changed a little as below:

Sum(

Aggr(

RangeSum(

    Above( Sum([Sum(Quantity)])

          , 0, 3)

)

, (PostingDate,(NUMERIC, ASCENDING)) )

)

this returns the same result than your formula plus the total sum.

[lzanetti]

That's right Andrea.

this formula gives the total as well, but still the values aren't correct.