Pivot table calculation of weighted average of percentage % with aggregation of subtotals - how to?

[#1]

Hi,

Am going in circles attempting to do a volume weighted average of an % value in a pivot table in QV 12.1.

(this % cannot be calculated from other data in the db, it is manually entered by a user, so the record in the db is a % value)

I provide a simplified QVD and some test data in associated QVD. Path to the QVD will need to be updated for anyone else to load it though. Also in the QVD I have locked it to showing only one awoId record to simply the view.

Below is a screencap of the attached Excel doc which shows the weighted average calculation I'm hoping to achieve, 82.815% for this particular awoId.

Data in the pivot is aggregated by dimensions of ProdCalWeek, createdDate, awoId and markerRecordId and Size.

Each awoId contains multiple markerRecordIds.

A "Marker Efficiency %" value exists for each markerRecordId.

I wish to weight this "Marker Efficiency %" by "Units cut" / "Total Units cut" so as to weight the efficiency more strongly when its  proportion of units of the total is greater, and vice versa.

Taking the first row as an example this means 82.8 * (102/1584)

then I would Sum all the rows under Weighted Average Effic% to get to a Weighted Average % for the awoId

I have tried many ways in both the script and in the chart but am not getting a working result.

The attached QVD shows me this at present:

In the loaded data:

"Units cut" = quantityCutPerPly * plies

"Factor" = "Efficiency" * "Units cut"/"Total units cut"

If I then could Sum all the factors for each awoId I would get to the weighted average % for that awoId

so to get the weighted average for each awoId I'm expecting to do something like the following:

Aggr(Sum (markerEfficiency * quantityCutPerPly*plies / Sum(quantityCutPerPly*plies),awoId)

I would like to have a Weighted average % display for each markerRecordId when the pivot table is expanded, then when closed 1 level down to show the Weighted average % for each awoId, then when closed to the createdDate level to show the Weighted average % for each createdDate, and finally to show the Weighted average % for each ProdCalWeek.

Please can someone suggest a solution. Thanks

[sunny_talwar · Accepted solution]

How about this:

Sum(Aggr((markerEfficiency * quantityCutPerPly*plies)/((Sum(TOTAL <awoId> quantityCutPerPly*plies)/Count(Size))), ProdCalWeek, createdDate, awoId, markerRecordId, Size))/Count(DISTINCT awoId)

[#3]

99% there 🙂

In initial testing if I do this:

If(Dimensionality()>=4,markerEfficiency,

Sum(Aggr((markerEfficiency * quantityCutPerPly*plies)/((Sum(TOTAL <awoId> quantityCutPerPly*plies)/Count(Size))), ProdCalWeek, createdDate, awoId, markerRecordId, Size))/Count(DISTINCT awoId)

)

I get the answers I'm expecting, since at the detail pivot drill down levels of Dimensionality 4 and 5 displaying the straight average (which is the value in the db field) is correct, with the weighting kicking in at the summary levels above the awoId aggregation level, so the above works for me.

Thank you very much Sunny for your persistence in figuring this one out. Very glad you're on my team today :-))