topic Pivot Table Total Problem in QlikView

Pivot Table Total Problem

Sat, 15 Aug 2009 04:37:41 GMT

I have a pivot table with a few dimensions and one expression. The expression is something like:

if(field = 'R',-Sum(sales),Sum(Sales))

The detail in the pivot table show the negative numbers properly. However the "Total" amount in the pivot table is not taking into account that some amounts are negative and is showing the total as if all number are a positive number.

Any Ideas what is wrong?

Thanks,
Stephen

Pivot Table Total Problem

johnw — Sat, 15 Aug 2009 04:51:37 GMT

On the expressions tab, change the total mode to sum of rows. The default is "expression total", but since you have multiple values for your field, field = null. Null does not equal 'R', so it chooses just sum(Sales) for your total value, which is of course not what you wanted. Summing up the rows instead should solve the problem.

Pivot Table Total Problem

Sat, 15 Aug 2009 05:01:41 GMT

It is grayed out.

Pivot Table Total Problem

johnw — Sat, 15 Aug 2009 05:30:29 GMT

Oh, right, pivot table. This should work, then, as it essentially manually sums up the rows inside of the expression, which is what we wanted to do by selecting sum of rows.

sum(Sales * if(field='R',-1,1))

Or like a normal person might write it:

sum(if(field='R',-sales,sales))

Pivot Table Total Problem

hector — Sat, 15 Aug 2009 05:41:01 GMT

If "field" is part of the table

if(field = 'R',-Sum(sales),Sum(Sales))

would work fine, if not

sum(Sales * if(field='R',-1,1))

will do

like john said, but 1st option will misscalculate the total row

C ya!

Pivot Table Total Problem

Oleg_Troyansky — Mon, 17 Aug 2009 11:07:15 GMT

The reason for the miscalculation is because the "field" is not uniquely defined at the total level. Hector's second formula should work fine, however might be heavy on a large data set.

I'd recommend calculating the corresponding "flag" in the script:

if(field = 'R', -1, 1) as SalesMult

Expression = sum(Sales * SalesMult)

Oleg

Pivot Table Total Problem

johnw — Tue, 18 Aug 2009 03:12:04 GMT

Or avoid the on the fly IF or multiplication entirely:

if(field='R',-sales,sales) as SignedSales

expression = sum(SignedSales)

Compression should keep that from causing any memory problems.

Pivot Table Total Problem

Tue, 18 Aug 2009 22:43:30 GMT

Look at the attached document and my note. What is happening?

Thanks,
Stephen

Pivot Table Total Problem

Tue, 18 Aug 2009 23:04:31 GMT

Out of curiosity can you try the following in the script, I had a similar problem a while back....

SalesDocItem_temp:
LOAD
[Subtotal 6_KZWI6] as OrderAmount,
MaterialNumber_Key,
SalesDocument_Key
FROM (qvd)
Where MaterialNumber_Key ='000000037000002000';

concatenate
LOAD
[Sales doc.type_AUART] as OrderType,
[Document date_AUDAT] as DocumentDate,
SalesDocument_Key
FROM (qvd);

SalesDocItem:
NOCONCATENATE LOAD
SalesDocument_Key,
OrderAmount,
MaterialNumber_Key,
OrderType,
DocumentDate

RESIDENT SalesDocItem;

DROP table SalesDocItem_temp;

Pivot Table Total Problem

Tue, 18 Aug 2009 23:40:10 GMT

Getting an error NoConcatenate part. Says table not found SalesDociItem etc.

Pivot Table Total Problem

hector — Tue, 18 Aug 2009 23:48:54 GMT

Hi, I dont know, but the resident should be from SalesDocItem_temp or not?

c ya

Pivot Table Total Problem

Tue, 18 Aug 2009 23:54:10 GMT

Yes, I changed it and am still getting an error. I'll keep looking at.

Pivot Table Total Problem

hector — Wed, 19 Aug 2009 00:08:32 GMT

the same error about noconcatenate?

Pivot Table Total Problem

Wed, 19 Aug 2009 00:19:22 GMT

No, different error, but I got the script to work and unfortunately it did not fix the problem.