12 Replies Latest reply: Nov 11, 2014 6:49 PM by David Young

# Pivot table Grand Total Question

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

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

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

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

Hi David,

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

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

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

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

David

• ###### Re: Pivot table Grand Total Question

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

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

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

David

• ###### Re: Pivot table Grand Total Question

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

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.