topic Re: Show Average in Horizontal Pivot Table in QlikView

Show Average in Horizontal Pivot Table

yashcena — Mon, 31 Jul 2017 07:48:13 GMT

I have a horizontal pivot table which has Months as dimension and 4 expressions.

I have to show the average of 4 expressions for the 12 month period (which is the dimension) at the last.

In the expression tab of the properties screen, I tried to change the Total Mode to Average of Rows but that option is grey'd out for pivot tables.

I have to show the Average just like the Total i.e at the end.

I tried using dimensionality but could not get the expression right. Can somebody help me with this?

Thanks,

YASH

Re: Show Average in Horizontal Pivot Table

Frank_Hartmann — Mon, 31 Jul 2017 08:29:56 GMT

Is this what you want to achieve?

Re: Show Average in Horizontal Pivot Table

Kushal_Chawda — Mon, 31 Jul 2017 10:03:12 GMT

see the attached

Re: Show Average in Horizontal Pivot Table

yashcena — Mon, 31 Jul 2017 10:35:11 GMT

Yes ...this is exactly what I was looking for.

Thank you so much!

Can you tell me what you did for it?

Re: Show Average in Horizontal Pivot Table

yashcena — Mon, 31 Jul 2017 10:35:48 GMT

Can you tell me what you did for it? I wanted to change the label to 'Average' instead of 'Total'.

Thank you!

Re: Show Average in Horizontal Pivot Table

Kushal_Chawda — Mon, 31 Jul 2017 10:39:15 GMT

You can go to presentation tab -> For head count -> Label for total , put 'AVG'

Re: Show Average in Horizontal Pivot Table

Kushal_Chawda — Mon, 31 Jul 2017 10:41:10 GMT

Also you wanted the avg numbers instead of Total, in attached QCW I have changed the expressions to get the Avg number

Re: Show Average in Horizontal Pivot Table

yashcena — Mon, 31 Jul 2017 10:42:21 GMT

Thank you! Actually, I wanted to know what you did that the 'Subtotal at Bottom' option is taking average and not sum.

I'm a beginner in qlikview, forgive me for asking trivial question!

Thanks..Yash

Re: Show Average in Horizontal Pivot Table

yashcena — Mon, 31 Jul 2017 10:43:36 GMT

Ah..got it now

thanks again!

Re: Show Average in Horizontal Pivot Table

Kushal_Chawda — Mon, 31 Jul 2017 10:47:48 GMT

I have used Dimensionality() function, when it is zero expression for avg will work else normal expression. Refer the expressions in attached QVW file

Re: Show Average in Horizontal Pivot Table

sandipahire — Sun, 11 Oct 2020 14:25:24 GMT

How to Get the Average?