topic Pivot total value different from sum of rows value in QlikView

Pivot total value different from sum of rows value

paolojolly — Wed, 10 Jun 2015 11:03:35 GMT

Hi, I'm not very experienced in QlikView and I need help to test a formula to calculate annual sales from sales data of quarter

The problem is that pivot total value is different from sum of rows value

for example in the grid

28036 + 1280 is different from total 29193

Can someone help me?

thanks

Re: Pivot total value different from sum of rows value

PrashantSangle — Wed, 10 Jun 2015 11:05:11 GMT

Hi,

Try AGGR()

Sum(AGGR(SUM(YourExpression),Dimesion))

Regards

Re: Pivot total value different from sum of rows value

paolojolly — Wed, 10 Jun 2015 12:15:44 GMT

Thanks, I tried your solution but not working

Re: Pivot total value different from sum of rows value

hic — Wed, 10 Jun 2015 14:23:44 GMT

Yes, the numbers are different - as they should be: You have a fairly complex formula in PONDERATA TRIM that contains a ratio between two aggregations, like

Sum( <calculation1> ) / Sum( <calculation2> )

This formula is calculated first for "ITALIA", then for "EXPORT", and finally for the entire scope. Such a calculation is not linear so there is no reason that the calculation for the entire scope should be the sum of the calculations for the smaller scopes.

Compare with a calculation of gross margin in percent:

Sum( GrossMarginInDollars ) / Sum( TurnoverInDollars )

There is no reason that this number for the entire company should be the sum of the different departments.

HIC

Re: Pivot total value different from sum of rows value

paolojolly — Thu, 11 Jun 2015 08:47:29 GMT

Thanks, I understand
Can you suggest me a way to have as total the sum of the values of the lines? In my example I would like to have 29316?

Is there some other formula to consider in forecact result the weight of the quarter?

thanks again

Re: Pivot total value different from sum of rows value

hic — Thu, 11 Jun 2015 08:57:06 GMT

The simplest way is to do the following:

Change the chart type to a straight table (Properties - General - The lower table icon)
Change the Total mode to "Sum of Rows" (Properties - Expressions - Total mode)

An alternative way, is to change the formula to an Aggr() function:

Sum( Aggr( <your current expression> , C_MERCATO_STORICO,D_DIREZIONE_FLD ))

This however will affect performance negatively.

HIC

Re: Pivot total value different from sum of rows value

paolojolly — Fri, 12 Jun 2015 10:40:37 GMT

I will follow your input

thanks