topic Re: Totaling wrong in a pivot table? in QlikView

Totaling wrong in a pivot table?

Fri, 20 Jul 2012 14:59:05 GMT

Does anyone know what might cause the issue I'm having with the total? How would I make the table sum of rows when you drill down? I'm referencing the attached png.

Re: Totaling wrong in a pivot table?

erichshiino — Fri, 20 Jul 2012 15:09:58 GMT

Hi, Brandon

Can you post here your expressions and maybe some data sample? Your data model might be resulting in this Pivot table total

Regards,

Erich

Re: Totaling wrong in a pivot table?

brenner_martina — Fri, 20 Jul 2012 15:22:55 GMT

Hi Brandon,

do you have any synthetic keys in your data model?

Greetings from Munich

Martina

Re: Totaling wrong in a pivot table?

Fri, 20 Jul 2012 16:29:19 GMT

Here is the expression for the column that is off:

if(column(1)<>0,1,0)*sum(IMYUNS*IPMLOT*IPMLGT*IPMWGT/1000)

Re: Totaling wrong in a pivot table?

Fri, 20 Jul 2012 16:29:46 GMT

No Munich, no synthetic keys.

Re: Totaling wrong in a pivot table?

Fri, 20 Jul 2012 16:31:20 GMT

I think the expression kind of answers the problems which is why I wanted to know if there is a way to sum rows in a pivot table?

Re: Totaling wrong in a pivot table?

edg_baltazar — Sat, 21 Jul 2012 07:11:33 GMT

Hello brandon , the think is that in pivot tables you can't choose the total mode like in the straight table where you can choose between different total modes, in pivot table the total mode is the Expresion Total, if you want to make a sum of rows you need to use aggr function in the qlikview reference manual there is a simple example of this, regards!

Re: Totaling wrong in a pivot table?

edg_baltazar — Sat, 21 Jul 2012 07:17:15 GMT

The problem is that in your expression you have one if statement so row by row its ok , but when calculates the total of that expression it calculates if column(1) <> 0 for total , is not going to make the comparision row by row and the sum it like you spect , like i said you need to use aggr function in your expression. Regards