topic Re: 3-months Average in QlikView

3-months Average

Wed, 02 Sep 2015 15:37:42 GMT

Hi,

I have a simple dataset.

Customer Period   amount
A              201507   10
B              201507   8
A              201506   17
C              201506   19
C              201505   8
A              201505   16
B              201504   9
D             201503   10
....

I want to make a table with period as dimension.
For each period I want to calculate the average amount over the last 3 months

Period 201507 : average for 201507,201506 & 201505
Period 201506 : average for 201506,201505 & 201504
Period 201505 : average for 201505,201504 & 201503
Period 201504 : average for 201504,201503 & 201502
....

Any suggestions for the expression ?

Re: 3-months Average

Oleg_Troyansky — Wed, 02 Sep 2015 15:50:34 GMT

Hi Dirk,

there is a number of ways to approach this problem:

1. Using QlikView "Accumulation" feature, you can have your Expression get accumulated automatically. This will only work in a chart with the Period being the only Dimension.

2. You can use Conditional Aggregation. In this case, you can't use Set Analysis because Set Analysis s only calculated once per chart and cannot be sensitive to chart dimensions. Hence, you have to use the IF() function. This approach may work on a small dataset, but it's not scalable - IF statements get very slow with large datasets.

3. You can build the "As of Date" table and split your "Display" periods and your "Transaction" periods. Using the "As of Date" table, you can associate your display periods with the transaction periods and calculate a number of conditional flags - for example "Last 3 months" flag. There are many sources that describe how to build and use the "As of Date" table, including my blog.

I also described it in a lot of detail in my new book QlikView Your Business.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: 3-months Average

sunny_talwar — Wed, 02 Sep 2015 15:52:29 GMT

This:

Expression: =RangeAvg(Below(Sum(amount), 0, 3))

Re: 3-months Average

maxgro — Wed, 02 Sep 2015 15:53:48 GMT

and you can also read this

Calculating rolling n-period totals, averages o... | Qlik Community

Re: 3-months Average

ecolomer — Wed, 02 Sep 2015 16:03:20 GMT

You can see this