Pivot Table Average Calculation

[viveksairam89]

Qlikview 11 : Finding Average in pivot table , for example i have data                             

      LOAD * INLINE [

    Ranking, Name, Month, Value

    2, A,Jan,0

    5, A, Jan, 1.56

    2, A,Feb,0

    5,G,Feb,0.5

    5,G,Feb,0.76

    5,G,Feb,2.16

    4, H, Feb, 81.63

    6, I, Mar, 9.60

]; I am unable to get the correct average in my sub Total?

When i use the formula in expression Avg(Value)

Actually i should get February total average as 41.38

i am getting as 21.26

Thanks in Advance

[m_woolf]

There are 5 Values for Feb. Those 5 Values total 85.05.

The average would be 85.05/5 = 17.01. If you suppress zero values there are 4 Values.

The average would be 85.05/4 = 21.2625

[PradeepReddy]

As explained by Mike

1) If u want to consider the Zero record in calculation, use the expression   Avg(Value)

2) if u don't want to consider Zero records in calculating Avg, try this expression

    sum(Value)/count((IF(Value<>0,Value)))

[viveksairam89]

Hi Mike ,

Thanks for you response, As per your  calculation it is correct but when we see by the Name , Feb month as 2 name G and H and when we sum and do the average we get total average of

G=0.5+0.76+2.16= 1.14

H=81.63

Divide by 2 we get average= 41.38

In order to get this kind of result in pivot can you help me out

[viveksairam89]

Hi Pradeep,

Thanks for you response, As per your  calculation it is correct but when we see by the Name , Feb month as 2 name G and H and when we sum and do the average we get total average of

G=0.5+0.76+2.16= 1.14 

H=81.63

Divide by 2 we get average= 41.38

In order to get this kind of result in pivot can you help me out

[PradeepReddy]

try this expression..

Sum(Value)/Count(DISTINCT if(Value<>0,Name))

[PradeepReddy]

take 2 dimension Month, Name and try the bellow expression

if(Dimensionality()='2', sum(Value)/Count(if(Value<>0,Name)),sum(Value)/Count(distinct if(Value<>0,Name)))

[viveksairam89]

Hi pradeep ,

Same expression I used and I get the desired value but in one case for a month example Aug we have 2 values =1,1.93 for one Name 1+1.93=2.93 ,and for other names we have 0 value for Aug ,Its doing sum and displaying but its not finding the average in subtotal

For that scenario can you help me out

Note:

I am using subtotal in the presentation tab for the Name and month

[sasikanth]

HI,

Try this

=avg(Aggr(DISTINCT sum(Value)/Count(if(Name<>0,Name)), Name))

Or

=avg(Aggr(DISTINCT sum(Value)/Count(if(Name<>0,Name,Month)), Name))

[sunny_talwar · Accepted solution]

May be this:

=Sum(Aggr(Avg(Value), Month, Name))/Count(DISTINCT {<Value -= {'0'}>} Name)