topic Pivot sum problem in QlikView

Pivot sum problem

Thu, 27 Jan 2011 14:00:06 GMT

I have a problem with =sum using a Pivot table.

When I use =sum([Tour Allocation]) the resultant value is double what it should be (80 instead of 40)

When I use =[Tour Allocation] and put in some additional columns/rows fields to fully expand my data to the lowest level, the data is correct (40)

I have checked my source data, and dont appear to have any duplicate records - does anyone have any idea what is causing it to return as a duplicate value?

Thanks

Pivot sum problem

Thu, 27 Jan 2011 14:14:53 GMT

Sounds to me that you need to use the Aggr() function.

Say you have [Tour Allocation] values per field and can be either or .

Then using Aggr(Sum([Tour Allocation]),) would aggregate the sum of field [Tour Allocation] per value.

Or you need to use DISTINCT, because some value is taken several times to calculate the sum of field [Tour Allocation].

But perhaps it's best you show a bit more of your pivot table before we can give you the right answer 😉

Pivot sum problem

Thu, 27 Jan 2011 15:36:25 GMT

Thanks for your response Mark, but that doesnt seem to work either

I used =aggr(sum([Tour Allocation]),[UniqueTourID]) which still returned value "80"

If I change to =aggr(max([Tour Allocation]),[UniqueTourID]) it returns value "40"

Would this imply I have some data duplication or linkage problem which is causing the record to be counted twice?

Pivot sum problem

Thu, 27 Jan 2011 16:02:11 GMT

Hello Can you try the same expression in any Text Box or a straight chart instead of a Pivot...? It could be with the simple settings of the Pivot tableI guess, but if you use the same expression on a text object, and if it still returns 80 then please check the data model, otherwise its the settings around the Pivot.

Mama

Pivot sum problem

Thu, 27 Jan 2011 16:35:34 GMT

Thanks Mama for that tip - think it is pointing to a problem in my data model / source data which I now need to resolve!