topic Re: Conditional sum in QlikView

Conditional sum

Thu, 14 Feb 2013 13:21:50 GMT

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

Re: Conditional sum

Thu, 14 Feb 2013 13:30:25 GMT

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

Re: Conditional sum

Thu, 14 Feb 2013 13:36:59 GMT

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.

Re: Conditional sum

Thu, 14 Feb 2013 13:45:47 GMT

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

Re: Conditional sum

swuehl — Thu, 14 Feb 2013 13:53:42 GMT

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.

Re: Conditional sum

Thu, 14 Feb 2013 15:12:16 GMT

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

Re: Conditional sum

Fri, 15 Feb 2013 13:20:35 GMT

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.