Pivot table partial sum calculation

[#1]

Hi

I have a scenario, A pivot table with 2 dimension and 1 expression with average sales. In this chart "show partial sum" is enabled for second dimension which is not showing up the right numbers.

example:

Sales Month	Product	Sales
August 2014	Prd1	$84                      (calc: (84+85)/2 = 84.5 which is rounded to 84)
September 2014	Prd1	$85                      (calc : (85+86)/2 = 85.5 which is rounded to 85)
	Partial Sum	$85                      (calc: (84+85+85+86)/4=85 but per Aug & Sep Sales, it should show as 84+85/2=84.5                                              which should be rounded to 84.

In this scenario, The partial sum need to be calculated from the average sales values coming up for each months not the average of total sales. Please let me know if any suggestions.

Thanks in Advance!

[Anonymous]

Could you paste in your Sales expression ?

[Anonymous]

Don't forget that for a number exactly in the middle QlikView rounds it up.

You could frig it with something like this :

     avg ( round ( ( [Fact One] + [Fact Two] ) / 2 , -1 ) -0.0001 )

[#4]

Hi Bill,

   Thanks for the reply!

My calculation is sum(sales)/count(sales).

Could you please let me know what -1 and -0.00001 parameters will do.

Thanks!

[Anonymous]

The -1 forces it to round a number that is exactly in the middle down instead of up.

The -0.00001 makes it a tiddly bit smaller.because for a partial sum average I know of no way to control it so I would have to frig it.