Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey

View solution in original post

12 Replies
Not applicable
Author

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...

michael_anthony
Creator II
Creator II

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.

michael_anthony
Creator II
Creator II

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.

SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey
Not applicable
Author

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. 

SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey
Not applicable
Author

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

David

SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey
SergeyMak
Partner Ambassador
Partner Ambassador

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

Regards,
Sergey