topic Missing Duplicates in pivot table in QlikView

Missing Duplicates in pivot table

Wed, 19 Jan 2011 14:50:50 GMT

Hello,

Whenever I hide a column in a Pivot Table, any duplicated data that was in that column is now, not totaled up for example:

I have some entries that go something like this:

x1682 -1510416.67
5416.67
-1510416.67
-135583.33
-1510416.67
-41583.33
-1197916.67
-23083.33
-1197916.67

However, when I minimise x1682, the second two -1510416.67 and the first -1197916.67 is not calculated into the final total (which should be -6975000) I get -2756250. Note: there is no DISTINCT in the formula.

Has anyone else experienced this before and is there a way around it?

Thanks for your help in advanced

Mark

Missing Duplicates in pivot table

Oleg_Troyansky — Wed, 19 Jan 2011 16:14:36 GMT

You guessed the reason - because some dimensions are hidden...

The way around it (I think...) would be to use advanced aggregation and force the aggregation to happen for all the relevant dimensions, including the hidden ones. For example:

sum( AGGR(NODISTINCT sum(Sales), Dim1, Dim2, Dim3) )

Missing Duplicates in pivot table

Thu, 20 Jan 2011 10:40:51 GMT

Thanks Oleg,

I'm going to have a look into it today.

Mark