11 Replies Latest reply: Oct 17, 2014 3:03 AM by Bjorn Wedbratt

12 month roll

Hi,

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

Thomas

• 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.

Calculating rolling n-period totals, averages or other aggregations

Best,

Matt

• Re: 12 month roll

Hi Matt,

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

Ok, what about ignoring the date dimension like this:

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

Best,

Matt

• Re: 12 month roll

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

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

In this.

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

?

• 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!

• Re: 12 month roll

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

:

• Re: 12 month roll

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

• Re: 12 month roll

Yes:-)

• Re: Re: 12 month roll

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