3 Replies Latest reply: May 2, 2011 9:05 AM by Martina Brenner

# Correctly Calculating Pivot Totals based on Expressions

People,

I posted this as part of a rather long chain that a few users have been helping me with, but have reopsted this as a new post as I think a lot of people may not get to the 28th post in the other chain. Apologies if this is a breach of forum ettiquette.

I also need to know if it is possible to to do the following:

I have an expression that calculates volume utilisation by essentially taking the volume of a product in a location and dividing it by the total available volume of the location and multiply it by 100. Effectively percentage utilisation.
When the pivot table calculates a subtotal, how do I tell it to sum the product volume in all locations and divide by the sum of available volume of all locations? Instead of summing the records above?

From this example spreadsheet, I would expect QV to correctly calculates the 4.5cbm as it can see that the first two lines are for the same location and it should not double count them. The product count and the used volume are also correctly counted, but the Utilisation which should be 66.89 ((3.01/4.5)*100) is incorrectly calculated as the sum of the lines above to give 200.67.

Any ideas?

Thanks
Ian

• ###### AW:Correctly Calculating Pivot Totals based on Expressions

Hi, Ian,

see the attached file.

• ###### AW:Correctly Calculating Pivot Totals based on Expressions

Hi Martina,

Thanks for the worksheet - however, as I am currently still using a Personal Edition version of QV, I am unable to open the worksheet. Never even thought about that as I have been merrily uploading worksheets for everyone else to help me.

I am hoping that through my efforts that company I am contracting to will actually purchase licenses for the product. But as for now, I am a little stuck.

Regards
Ian

• ###### AW:Re: AW:Correctly Calculating Pivot Totals based on Expressions

Hi, Ian,

here the screenshot and expression:

If(Dimensionality()=0,
Avg(Aggr(Sum ([Product Count]*[Product Volume])/[Location Volume],Site,Location)),
Sum ([Product Count]*[Product Volume])/[Location Volume])

enter a new expression and: Dimensionality() to have a look at the right number of the partial sum level