topic Re: 12 month roll in Qlik Learning Discussions

12 month roll

Tue, 14 Oct 2014 16:43:59 GMT

Hi,

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

Thomas

Re: 12 month roll

Tue, 14 Oct 2014 18:22:00 GMT

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

Re: 12 month roll

Tue, 14 Oct 2014 19:05:24 GMT

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

Re: 12 month roll

Wed, 15 Oct 2014 18:40:43 GMT

Ok, what about ignoring the date dimension like this:

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

Best,

Matt

Re: 12 month roll

Wed, 15 Oct 2014 18:59:06 GMT

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

Hope it helps you!

Regards!

Re: 12 month roll

Thu, 16 Oct 2014 17:31:44 GMT

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

Thomas

Re: 12 month roll

Thu, 16 Oct 2014 17:34:04 GMT

In this.

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

Re: 12 month roll

Thu, 16 Oct 2014 17:41:40 GMT

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!

Re: 12 month roll

Thu, 16 Oct 2014 18:05:15 GMT

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

Re: 12 month roll

Thu, 16 Oct 2014 18:33:29 GMT

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

Re: 12 month roll

Thu, 16 Oct 2014 18:36:39 GMT

Yes:-)

Re: Re: 12 month roll

Bjorn_Wedbratt — Fri, 17 Oct 2014 07:03:31 GMT

Hi Thomas,

Not sure if you need this to be done using Set Analysis. Personally I prefer to generate aggregations like this in the script (but may be because I'm used to "the old way", before Set Analysis was even included in QlikView )

If you can live with generating it in the script, I attached a small example that I think you can tweak to your liking.

Best,

Bjorn