topic Re: Pivot Table Average problem in QlikView

Pivot Table Average problem

Wed, 02 Apr 2014 12:38:57 GMT

Hi I have brands and products in the dimension of the table. I want to take the average of their score in the pivot table:

Brand Name	Product Name	Score	avg
Brand 1	Product 1	100	250
Brand 1	Product 2	200	250
Brand 1	Product 3	300	250
Brand 1	Product 4	400	250

I want to make the table as shown above when the dimensions are all expanded. When I collapse the product name field, it works well but when I expand it, it will show the exact value of the score on the avg field.Can you help me doing this?

Thank you

Re: Pivot Table Average problem

MK_QSL — Wed, 02 Apr 2014 12:43:59 GMT

Try

Avg({1}TOTAL Score)

Re: Pivot Table Average problem

VishalWaghole — Wed, 02 Apr 2014 12:52:13 GMT

Hi,

Please find attached file, it will help you.

- Regards,

Vishal Waghole

Re: Re: Pivot Table Average problem

Wed, 02 Apr 2014 12:58:21 GMT

Brand Name	Product Name	Score	Avg
Brand 1	Prod 1.1	100	150
Brand 1	Prod 1.2	200	150
Brand 2	Prod 2.1	300	400
Brand 2	Prod 2.2	500	400

I forgot to mention that there are other brands in the table. Therefore I need to calculate all the brands' average individually.

Re: Pivot Table Average problem

its_anandrjs — Wed, 02 Apr 2014 13:01:19 GMT

Write in new expression for pivot table

Avg({1} ALL Score)

Re: Pivot Table Average problem

Wed, 02 Apr 2014 13:26:15 GMT

Hi Anand. It calculates the avg of the score of all brands but I need to take the average of only the corresponding brand.

Re: Pivot Table Average problem

MK_QSL — Wed, 02 Apr 2014 13:32:11 GMT

Try

AVG({1}TOTAL <[Brand Name]> Score)

AVG({TOTAL <[Brand Name]> Score)

Re: Pivot Table Average problem

its_anandrjs — Wed, 02 Apr 2014 13:43:59 GMT

Ok then try like

Avg(ALL <[Brand Name]> Score)

Re: Pivot Table Average problem

richard_pearce6 — Wed, 02 Apr 2014 13:57:56 GMT

Try avg(aggr(avg(Score),[Brand Name]))

Richard

qlikcentral | Understand / Create / Inform

Re: Pivot Table Average problem

Wed, 02 Apr 2014 14:11:43 GMT

Hi thanks for help. These expressions work but only when the product field is collapsed. When I expand the dimensions the avg(score) equals to the sum(score).

Re: Pivot Table Average problem

MK_QSL — Wed, 02 Apr 2014 14:14:03 GMT

AVG({1}TOTAL <[Brand Name]> Score)

Re: Pivot Table Average problem

michael_maeuser — Wed, 02 Apr 2014 15:16:57 GMT

avg(TOTAL score)

Re: Pivot Table Average problem

Roop — Wed, 02 Apr 2014 15:25:23 GMT

Sum(Score) / count(Product Name)

would work too in this example.

It depends on exactly what you want. The difficulty comes if some of the values are zero and they need to be counted as well.

Re: Pivot Table Average problem

Tue, 08 Apr 2014 15:26:07 GMT

Thanks for help. I solved it. The problem occured because of the calculated dimension. After copying calculated dimension to the set analysis. the avg function will work.