Conditional sum

[#1]

Hi everybody.

I've got an issue wih a conditional sum where I want to sum a chosen field dependong on the value of another field.

basically, I have the kind of 2 following table :

TABLE 1

Customer ID	FLAG
A	VR1
B	VR2
C	VR3
D	VR4

TABLE 2

DOC ID	Customer ID	VR1	VR2	VR3	VR4
xxxx	A	w	x	y	z
yyyyy	B	w	x	y	z

I've tried that :

sum( $(FLAG))

but it doesn't work.

Does one of you have an idea ?

Thanks

[#2]

Hi,

you can do conditional SUM in this way:

Sum ( {$<[Cumstemer ID]={'A'}>} FLAG)

In this way you're going to Sum the field FLAG only for CUSTOMER ID = 'A'

Hope it helps

Regards

Giampiero

[#3]

Thank you for your quick answer.

I need to give futher details.

The resulting expression will be used in a pivot table with one of the dimension beeing the customer ID, in order to have the sum of the appropriate field with respect to the customer id for all customers.

So with your expression, I need to have one expression for each customer which is not what I need.

[#4]

Hi,

could you please tell me wich field you want to Sum and the condition you want to use?

In this way I can help you.

Do you have only Customer Id as dimension in your pivot table ?

Regards

Giampiero

[swuehl]

You can try using a CROSSTABLE LOAD on your table2, something like

CROSSTABLE (FLAG, VALUE, 2) LOAD [DOC ID], [Customer ID], VR1, VR2, VR3, VR4 resident TABLE2;

drop table TABLE2;

And maybe create a Key from Customer ID and FLAG to avoid the synth key.

[#6]

The resulting pivot table will have the customer ID as 1st dimension and months as 2nd dimension.

I could not have a cross table because normally, at the end I should have one value (VR1 to VR4) associated with one customer ID.

Currently, one solution is the following expression, but it's not really smart:

=	if(Flag='VR4', sum( {$<DO_TYPE={'5','6'},[Ligne HT]=-{'0'}>} if([Entete Date Livraison]<='14/02/2013',VR1,VR4)),
	if(Flag='VR1', sum( {$<DO_TYPE={'5','6'},[Ligne HT]=-{'0'}>} VR1),
	if(Flag='VR2', sum( {$<DO_TYPE={'5','6'},[Ligne HT]=-{'0'}>} if([Entete Date Livraison]<='14/02/2013',VR1,VR2)),
	if(Flag='VR3', sum( {$<DO_TYPE={'5','6'},[Ligne HT]=-{'0'}>} if([Entete Date Livraison]<='14/02/2013',VR1,VR3))))))

[#7]

Unfortunately, I have an issue with this expression when I want to use it in a chart without the customer ID dimension. In this case I can't have the sum chart.