Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

12 month roll

Hi,

Can anyone help me create an expression for measuring 12 rolling sales.

Thomas

11 Replies
Not applicable

Re: 12 month roll

Hi Thomas,

Here's an example of a 3 month rolling average using the rangesum and above functions. Hopefully you can adapt this to you setup.

This article by gwassenaar  is also a great resource:

Calculating rolling n-period totals, averages or other aggregations

Best,

Matt

Not applicable

Re: 12 month roll

Hi Matt,

Thanks for your reply.

It issue with this is that when you choose a specific month it shows the sales for that month and not the 3 month rolling avg.

Regards,

Thomas

Not applicable

Re: 12 month roll

Ok, what about ignoring the date dimension like this:

rangeavg(above(sum({<YourDateField = >}Sales),0,3))

Best,

Matt

Not applicable

Re: 12 month roll

Hi Thomas, try with this set analysis in your expression: Month= {">=$(=max(Month)-12)<=$(=max(Month))"}

Hope it helps you!

Regards!

Not applicable

Re: 12 month roll

Capture.PNG.png

I can't really ignore the date dimension since I will choose a specific month to see numbers for that month.

Thomas

Not applicable

Re: 12 month roll

In this.

Rangeavg(above(sum({<Month = >} Sales), 0,12))

?

Not applicable

Re: 12 month roll

There is a little example, I use Q (1,2,3,4) as dimension, and a simple sum with the expression given in the last post. The dimension will be filtered by the range in the expression

Hope it helps!

Regards!

rangesum.png

Not applicable

Re: 12 month roll

Capture.PNG.png

Hmm....this gives me the same as a simple SUM.

:

Not applicable

Re: 12 month roll

So you need a total InvoiceQuantity of the last 12 months in a single row?

Community Browser