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]

and please refer to this thread:

YTD, MTD issue

where I tried to explain step by step some of the basc time functions in Qlik !

Hope these could help you Luigi !

Omar BEN SALEM

[lzanetti]

@Aehman

I want the sales over 12 month, up to the indexed month by default. The, if I select a product, he plot should show sales restricted to that product.

It's neither about sales of PYTD, nor sales YTD, it's sales on 12 months backwards.

A sample data looks like this:

Quantity	Posting Date	Product
3	31/07/2017	prod A
20	28/07/2017	prod A
50	28/07/2017	prod A
4	28/07/2017	prod A
9	27/07/2017	prod A
7	26/07/2017	prod B
3	26/07/2017	prod B
16	26/07/2017	prod B
4	24/07/2017	prod B
30	21/07/2017	prod C
5	21/07/2017	prod C
5	19/07/2017	prod C
7	18/07/2017	prod C
54	18/07/2017	prod A
6	18/07/2017	prod A
1	18/07/2017	prod A
1	18/07/2017	prod B
2	04/07/2017	prod B
1	21/07/2017	prod C
1	13/07/2017	prod C
75	29/07/2017	prod D
40	28/07/2017	prod D
5	28/07/2017	prod D
5	28/07/2017	prod D
6	27/07/2017	prod D
1	26/07/2017	prod B
3	26/07/2017	prod B
5	26/07/2017	prod B

for monthly sold product:

DIMENSION: PostingDate.autoCalendar.YearMonth

MEASURE: Sum(Quantity)

I need to create a measure for the "Products on market" sold over 12 months backwards, month by month:

[lzanetti]

@omar bensalem, the "Calculating rolling n-period totals, averages or aggregations" looks fine, I read it earlier, but it's referred to Qlik View. How do I translae it to Qlik Sense?

all the proposed formulas don't work

[OmarBenSalem]

The example above has been implemented with Qlik Sense; (the example with my proper master calendar)

You should have tested it... I even attached an image to show you it does what you want to achieve

[lzanetti]

omarbensalem‌ I read your explanation on syntax and it made a lot of light, thank you very much.

unfortunately, I'm still not able to get what I need.

[lzanetti]

Ok, I didn't test it because I don't know where to paste alla that code.

[agigliotti]

Hi Luigi,

For your request you should use below expression:

=sum( {< Date = {">=$(=MonthStart(AddMonths(Today(), -12)))<=$(=MonthEnd(AddMonths(Today(), -1)))"} >} Amount )

what above for time period: nov '16 - oct '17

[lzanetti]

Hi Andrea, I saw your reply, it looks fine, but it gives me all 0 as a result.

The same result I get from

sum({<Date.Autocalendar.YearMonth=,Date.Autocalendar.Year=,Date.Autocalendar.Month=,Date.Autocalendar.Date=,

Date.Autocalendar.Month={">=$(=addMonths(max(Date.Autocalendar.Date),-12))<=$(=max(Date.Autocalendar.Date))"}>}Quantity)

proposed by Omar.

One problem I realized I had, in the meanwhile, was: my field's name was "Posting Date" so in uploading to QS it became "Posting.Date" this made confusion I guess (because Date was recognized as a function).

I changed it to PostingDate

[agigliotti]

Hi Luigi,

At this point I think the quickest way to fix it is posting a sample qvf file.

[lzanetti]

How can I get it?

I'm using QS Desktop