topic Re: Pivot table avrage calculation in QlikView

Pivot table avrage calculation

amit_saini — Fri, 30 May 2014 09:29:30 GMT

Hi Folks ,

I'm using pivot table as mentioned below:

Here subtotal at the bottom is is incorrect , I was looking for option in my table as below for Total mode please see below . but looks like it is disabled . Kindly help me how to get correct total or average at bottom .

Thanks,

Re: Pivot table avrage calculation

tresB — Fri, 30 May 2014 09:33:14 GMT

Change your expression to something like:

=Sum(Aggr(Avg(AmtField) , Dimension))

Re: Pivot table avrage calculation

amit_saini — Fri, 30 May 2014 09:44:04 GMT

Hi Tresesco,

I know this option , but my calculated expressions are some how big like below:

if(([Available time]-[Downtime Planned])=0, num(0, '#,#0%'),

([Available time]-[Downtime Planned]-[Downtime Unplanned])

([Available time]-[Downtime Planned])

)

Is there any easy way as we have it straight table to select automatically as Sum or Average etc...

Thanks,

Re: Pivot table avrage calculation

tresB — Fri, 30 May 2014 09:49:51 GMT

In a pivot, I don't see an easier way. However, your if(..) expression too can be used similarly as I mentioned above.

Re: Re: Pivot table avrage calculation

amit_saini — Mon, 02 Jun 2014 05:55:00 GMT

Hi Tresesco,

Kindly see the attachment.

Please help me for Availability , Performance , Quality and OEE total sum calculation at the bottom.

Thanks,

Re: Pivot table avrage calculation

Mon, 02 Jun 2014 06:23:30 GMT

Hi AS,

If your current selection is as shown below. Then what should be your answer for Availability , Performance , Quality and OEE.

Regards

Garry

Re: Pivot table avrage calculation

jagan — Mon, 02 Jun 2014 06:23:57 GMT

HI,

What is your expected values for the total? Sum of all rows?

Regards,

Jagan.

Re: Pivot table avrage calculation

amit_saini — Mon, 02 Jun 2014 06:27:20 GMT

Hi Jagan,

Yes it should be sum of all rows. It is coming perfect for all calculated expression except last 4 in pivot table.

Thanks,

Re: Re: Re: Pivot table avrage calculation

tresB — Mon, 02 Jun 2014 06:45:04 GMT

Amit,

With aggr() the other expression label/column reference would not work. Use variables to declare the expressions and use them in Aggr() like I did in attached qvw. I did it for 'Availability'.