Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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...
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.
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.
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
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.
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
Gottcha...Here is a copy of my App. Let me know what you discover.
David
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
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