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

Pivot total value different from sum of rows value

paolojolly — Wed, 07 Feb 2018 18:00:24 GMT

Hi, I'm not very experienced in QlikView and I need help in this pivot table

Total value € 1.107,82 is different from from sum of rows value

€ 423,70+

€ 494,72+

€ 182,43+

€ 7,72=

€ 1.108,58

and i don't understand why

Can someone help me?

thanks

Re: Pivot total value different from sum of rows value

swuehl — Wed, 07 Feb 2018 18:09:06 GMT

The partial sum is evaluated in total context, if you need to add up the sum of rows in a pivot table, use advanced aggregation:

=Sum ( Aggr(

YourExpression

, YourTableDimension(s)

))

Re: Pivot total value different from sum of rows value

paolojolly — Thu, 08 Feb 2018 07:37:54 GMT

Thanks for the reply but there is something that I do not understand

If I try to do total counts of data without dimension(see table Total data) I get a value(1062.84) different from the one obtained using AGGR (1.063,01)

And I think the correct value is 1062.84

Thanks again

Re: Pivot total value different from sum of rows value

laujerry — Thu, 08 Feb 2018 08:43:03 GMT

this is likely because your transaction can map to more than one dimension

e.g. certain transaction may both associate to R1 and R11, so it is counted in both rows

Re: Pivot total value different from sum of rows value

lorenzoconforti — Thu, 15 Feb 2018 14:01:53 GMT

I think the solution Stefan has proposed is the correct one (i.e. it provides the correct totals, which is 1063.01 not 1062.84)

I believe the issue is both with your data and with your expressions

You have a "D_AGENTE_DEST" (F9) that is missing "W_TOT_IMP_RIGA_NETNET" for a few months

The implication of this is clear when you divide by a number that you calculate with a series of if statements; look at the expression "dati altri trim" in the "Total Data" table. You are dividing by the following expression:

if(sum(IF(ANNO_DOC = (vCurrentYear-5) AND MAKEDATE((vCurrentYear-5) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3), W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +

if(sum(IF(ANNO_DOC = (vCurrentYear-4) AND MAKEDATE((vCurrentYear-4) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3), W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +

if(sum(IF(ANNO_DOC = (vCurrentYear-3) AND MAKEDATE((vCurrentYear-3) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3), W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +

if(sum(IF(ANNO_DOC = (vCurrentYear-2) AND MAKEDATE((vCurrentYear-2) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3), W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0) +

if(sum(IF(ANNO_DOC = (vCurrentYear-1) AND MAKEDATE((vCurrentYear-1) , 1, 1) >= MakeDate(2009, 1, 1) AND 2 = Ceil(MESE_DOC/3), W_TOT_IMP_RIGA_NETNET, 0)) <> 0, 1, 0)

Now, when you activate the dimension in your table, the expressions (and not the totals) are calculated correctly. The expressions above return the correct values for all "D_AGENTE_DEST"; for F9 it correctly returns 3 while it returns 5 for all other "D_AGENTE_DEST". When QlikView evaluates the totals for the expression, the above will return 5 across the whole data set (i.e. every if statement returns true because, across the whole dataset, there are no gaps of "W_TOT_IMP_RIGA_NETNET" in any month; again, this happens because the "D_AGENTE_DEST" dimension is ignored when calculating the totals). Which is incorrect because we know there is "D_AGENTE_DEST" whose result is 3 so the above should return something lower that 5.

So, the solution, as stated by Stefan, is to introduce the aggregation; the total you were calculating before in your text box (1062.84) was actually incorrect

Hope it helps

Re: Pivot total value different from sum of rows value

paolojolly — Thu, 15 Feb 2018 14:49:06 GMT

Thanks for the reply
I have just one doubt:

If I create pivot tables with different dimensions, I have to change the list of dimensions present in the aggr function for each pivot table? if I do so I will have on each pivot table total values different from the others?

Thanks again

Re: Pivot total value different from sum of rows value

lorenzoconforti — Thu, 15 Feb 2018 15:05:01 GMT

"I have to change the list of dimensions present in the aggr function for each pivot table?" - I believe so; you would need to list the dimensions used

"if I do so I will have on each pivot table total values different from the others?" - the totals should be the same but your expressions are quite complex so you'll need to understand what extra dimensions you are bringing in and what impact it will have on the calculations. Take for example the division discussed before; that relies on the data being presented without "ANNO_DOC" or "MESE_DOC" as dimensions otherwise the denominator won't be calculated correctly. Probably you would need to test it to ensure you are getting the desired results