topic Pivot table partial sum calculation in QlikView

Pivot table partial sum calculation

Sun, 16 Nov 2014 17:05:20 GMT

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!

Re: Pivot table partial sum calculation

Anonymous — Sun, 16 Nov 2014 17:13:08 GMT

Could you paste in your Sales expression ?

Re: Pivot table partial sum calculation

Anonymous — Sun, 16 Nov 2014 17:33:24 GMT

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 )

Re: Pivot table partial sum calculation

Sun, 16 Nov 2014 21:26:51 GMT

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!

Re: Pivot table partial sum calculation

Anonymous — Sun, 16 Nov 2014 21:40:51 GMT

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.