topic Re: Pivot table Grand Total Question in QlikView

Pivot table Grand Total Question

Mon, 10 Nov 2014 20:23:28 GMT

I have created the following command to give me an average for each of my nine shops:

avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop,TrnsDesc))

This works perfectly. What I want to do now is create an avg of these 3 shops. So if:

avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop,TrnsDesc)) produces:

Carp - 1.20

Elec - 1.15

Plumb - 1.16

I want to see following:

Carp - 1.20

Elec - 1.15

Plumb - 1.16

Avg Total - 1.17

Can I do this in one pivot graph?

Thanks,

David

Re: Pivot table Grand Total Question

Mon, 10 Nov 2014 20:34:11 GMT

Here is a picture of what I have so far:

So in this example I want to Avg 4.25.1.84, and 6.61 together and so on...

Re: Pivot table Grand Total Question

michael_anthony — Tue, 11 Nov 2014 00:13:38 GMT

If I understand your query, at the Shop level you want to get the average of the TransDesc results. At the Total level you want to Average the Averages of each shop, rather than a weighted average of each TrnsDesc.

You could wrap the expression in another aggr:

avg(aggr(

avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop,TrnsDesc))

,Shop))

The inner Avg(Aggr will calculate the internal result of average of the TrnsDesc for the shop. The outer will then take the Average of those results.

On issue may be that it will not longer show the result for each TrnsDesc, but only one per shop (as the Aggr level is too high now, it can only return one total value per shop).

To get around you could use the Dimensionality() function as in:

If(Dimensionality()=0, new expression, existing expression). That will tell it to only do the more complex expression on the grand total row.

Re: Pivot table Grand Total Question

michael_anthony — Tue, 11 Nov 2014 00:15:15 GMT

Note:

You may need to put

{$ <TrnsDateYear={2013}>} into the outer Avg function if the selections remove those records - I haven't tested that component.

Re: Pivot table Grand Total Question

SergeyMak — Tue, 11 Nov 2014 05:11:14 GMT

Hi David,

can you share your app.

Try this:

IF(dimensionality()=0,

avg(aggr(

avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop,TrnsDesc))

))

avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop,TrnsDesc))

)

and you have to enable Partial Sum for the first column

Regards,

Sergey

Re: Pivot table Grand Total Question

Tue, 11 Nov 2014 12:48:39 GMT

I tried your suggestion and it cleared out all the rows except for the avg of all the shops total line. The problem with my app is I am pulling data from multiple qvds and excel files so posting it would be difficult unless you dont need to reload.

Re: Pivot table Grand Total Question

SergeyMak — Tue, 11 Nov 2014 13:27:20 GMT

I don't think that I need to reload your app. Anyway, we always have a binary load option.

I just need to look at this pivot table.

Regards,

Sergey

Re: Re: Pivot table Grand Total Question

Tue, 11 Nov 2014 13:44:43 GMT

Gottcha...Here is a copy of my App. Let me know what you discover.

David

Re: Pivot table Grand Total Question

SergeyMak — Tue, 11 Nov 2014 14:04:07 GMT

David,

Try this expression

IF(dimensionality()=0,

avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop))
,
avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop,TrnsDesc))
)

Looks promising..

Regards,

Sergey

Re: Pivot table Grand Total Question

SergeyMak — Tue, 11 Nov 2014 14:20:32 GMT

But the following is definitely correct. I checked additionally in Excel

IF(dimensionality()=0,

Avg(Aggr((avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop,TrnsDesc))),Shop))

,
avg(aggr(sum({$ <TrnsDateYear={2013}>} Quantity)/count({$ <TrnsDateYear={2013}>} WorkOrderID),Shop,TrnsDesc))
)

Sergey

Re: Pivot table Grand Total Question

Tue, 11 Nov 2014 14:29:00 GMT

Thank you so much. It worked. This is awesome.

David

Re: Pivot table Grand Total Question

SergeyMak — Tue, 11 Nov 2014 22:37:39 GMT

Hi David,

To help other people with similar issues, could you mark the answers as correct and helpful, if you find a solution.

Regards,

Sergey

Re: Pivot table Grand Total Question

Tue, 11 Nov 2014 23:49:35 GMT

Hi Sergey

THank you for the reminder. I need to mark the second one right because that was the fix. I will do it in a bit.