topic Re: Just an Average ! ? in QlikView

Just an Average ! ?

Thu, 25 Jun 2015 10:24:19 GMT

Hi,

I have a Pivot table with a set of Complex calculation. I will try to make my question as simple as possible.

The table looks something like;

Year	2013	2014	2015	2016
Duration
5	1	2	1	1
10	1.5	1	2	2

The Table is a Pivot Table, hence Duration and Year are dimensions.

The requirement is to get the Average of [curr Year, cur Year+1, cur Year+2 ] for every year / duration

Thus, the table should look like;

Year	2013	2014	2015	2016
Duration
5	1.33	1.33	0.66	0.33
10	1.5	1.66	1.33	0.66

Appreciate any help !

Thanks

swuehl — Thu, 25 Jun 2015 10:34:08 GMT

Try something like

=Rangesum( YourExpression, after(YourExpression,1) after(YourExpression,2) ) / 3

alexandros17 — Thu, 25 Jun 2015 10:35:36 GMT

Try with this expression:

RangeAvg(Value, After(Value), After(After(Value)))

sunny_talwar — Thu, 25 Jun 2015 10:40:11 GMT

Script:

Table:

LOAD * Inline [

Duration, Year, Data

5, 2013, 1

5, 2014, 2

5, 2015, 1

5, 2016, 1

10, 2013, 1.5

10, 2014, 1

10, 2015, 2

10, 2016, 2

];

Expression: =(Alt(Data, 0) + Alt(After(Data), 0) + Alt(After(Data, 2, 0), 0))/3

Output:

HTH

Best,

Sunny