Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
sergey_maka8
Valued Contributor

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

12 Replies
Not applicable

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

michael_anthony
Contributor II

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.

michael_anthony
Contributor II

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.

sergey_maka8
Valued Contributor

Re: Pivot table Grand Total Question

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

Not applicable

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. 

sergey_maka8
Valued Contributor

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

Not applicable

Re: Re: Pivot table Grand Total Question

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

David

sergey_maka8
Valued Contributor

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

sergey_maka8
Valued Contributor

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

Community Browser