topic Re: Pivot Table Average Calculation in QlikView

Pivot Table Average Calculation

viveksairam89 — Tue, 26 Jan 2016 18:27:40 GMT

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

Re: Pivot Table Average Calculation

m_woolf — Tue, 26 Jan 2016 19:35:04 GMT

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

Re: Pivot Table Average Calculation

PradeepReddy — Wed, 27 Jan 2016 05:21:07 GMT

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)))

Re: Pivot Table Average Calculation

viveksairam89 — Wed, 27 Jan 2016 11:00:10 GMT

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

Re: Pivot Table Average Calculation

viveksairam89 — Wed, 27 Jan 2016 11:00:48 GMT

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

Re: Pivot Table Average Calculation

PradeepReddy — Wed, 27 Jan 2016 11:15:57 GMT

try this expression..

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

Re: Pivot Table Average Calculation

PradeepReddy — Wed, 27 Jan 2016 11:36:25 GMT

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)))

Re: Pivot Table Average Calculation

viveksairam89 — Wed, 27 Jan 2016 11:37:18 GMT

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

Re: Pivot Table Average Calculation

sasikanth — Wed, 27 Jan 2016 11:41:41 GMT

HI,

Try this

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

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

Re: Pivot Table Average Calculation

sunny_talwar — Wed, 27 Jan 2016 11:47:15 GMT

May be this:

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

Re: Pivot Table Average Calculation

viveksairam89 — Wed, 27 Jan 2016 12:28:57 GMT

Thanks for the help everyone:)

Re: Pivot Table Average Calculation

Samrithi — Mon, 10 Jun 2019 17:46:30 GMT

Hi,

I need Avg for all the Years as below.

Year	Category1	Category2	Category3	Category4
Avg	2534.0%	786.0%	786.0%	786.0%
2017
Jan	600	700	660	300
Feb	50	50	50	50
Mar	5300%	56%	56%	56%
2018
Apr	100	140	500	900
Jan	40	40	40	40
Feb	2300%	2300%	2300%	2300%
Mar
2019
Jan	100	104	600	1900
Feb	20	20	20	20
Mar	2%	2%	2%	2%

Appreciate the above results.

Chart Association

Samrithi — Fri, 21 Jun 2019 17:19:25 GMT

Hi,

I have 2 charts (attached) those used calculated dimensions and when I select 1 bar in 1 chart(Last 30 days) then,

2nd chart (ActInAct Customers) is also applying filter and showing blank(attched).

I dont nees to change the 2nd chart should not reflect on any values and should show all the values.

below are the calc dimensions in 2nd chart (ActInAct Customers):

dimension:

=Aggr(if(CUSTOMER_IN_LAST_3_YEARS_IND='Y' and CUSTOMER_IN_LAST_2_YEARS_IND='N','Last 3Yrs Active but InActive Last 2yrs',
if(CUSTOMER_IN_LAST_2_YEARS_IND='Y' and CUSTOMER_IN_LAST_1_YEARS_IND='N','Last 2Yrs Active but InActive Last 1yr',
if(CUSTOMER_IN_LAST_1_YEARS_IND='Y' and CUSTOMER_IN_LAST_90_DAYS_IND='N','Last 1Yr Active but InActive Last 90days',
if(CUSTOMER_IN_LAST_90_DAYS_IND='Y' and CUSTOMER_IN_LAST_30_DAYS_IND='N','Last 90days Active but InActive Last 30days'))))
,CHK_CustID)

Measure:

count({1}CHK_CustID) - added 1 on measure also but still not working.

Appreciate the solution.

Thanks