Pivot Table Total Problem

[#1]

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

[johnw]

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.

[#3]

It is grayed out.

[johnw]

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))

[hector]

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!

[Oleg_Troyansky]

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

[johnw]

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.

[#8]

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

Thanks,
Stephen

[#9]

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;

[#10]

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