Pivot table - calculated Fiedls

M

MClaudio

I insert two calculated fields in this Pivot-Table :
Calculated Field 'Load' contain this formula =IF(Q.ty>0;Q.tà;0)
Calculated Field 'UnLoad' contain this formula =IF(Q.ty<0;Q.tà;0)

The "General Total" is wrong, he must be:
Load = 44 ( rather than 0)
UnLoad = -96 ( rather than -52)
Total Q.ty is right -52 ( Balance)

Why totals isn't correct ?
Data
Date Mov. Color Load UnLoad Tot.Q.ty
21/02/05 331 BIANCO 0,00 -18,00 -18
332 GHIACCIO 0,00 -26,00 -26
21/02/05 Total 0,00 -44,00 -44
22/02/06 331 BIANCO 18,00 0,00 18
332 GHIACCIO 26,00 0,00 26
22/02/06 Total 44,00 0,00 44
28/03/06 331 BIANCO 0,00 -19,00 -19
332 GHIACCIO 0,00 -33,00 -33
28/03/06 Total 0,00 -52,00 -52

General Total 0,00 -52,00 -52
 
M

MClaudio

Excuse me,
Formula is correct:
=IF(Q.ty>0;Q.ty;0)
=IF(Q.ty<0;Q.ty;0)

I forgot to change in my message Qt.à to Qt.y.
The problem is the same.

Claudio

"Herbert Seidenberg" ha scritto:
 
D

Debra Dalgleish

The grand total will do the same calculations as the individual records.
Since the grand total quantity is -52, that amount is calculated as the
grand total for unload, instead of a sum of the Unload amounts.

Instead of using calculated fields, you could add a field to the source
data, and calculate the type there. For example:

=IF(C2>=0,"Load","Unload")

Add that field to the Column area, with Quantity in the data area, and
you should get the results that you want.
 
Top