topic Re: Chart with a conditional sum in QlikView

Chart with a conditional sum

Tue, 24 Mar 2015 11:54:09 GMT

Dear all,

I have a chart and I'm facing problem with conditional sum sintax.

In particular I have an expression that says

Sum (BALANCE_CTV2)

Now I need that the expression sum only the values where the filed ACC_TYPE_DESCRIPTION is equal

At Bank

Cash Pooling

Restricted

Short Term Investment

As alternative it could be more easy to say to sum if it is not equal to one of this two values:

Committed

Credit Facilities Drawn

If i try this sintax with just one of the values it works

=SUM({<ACC_TYPE_DESCRIPTION ={'At Bank'}>}BALANCE_CTV2)

but when i try to add the other values with the OR operator it doesn't work

Could someone tell me how is the sintax to include or exclude more then one value?

Thank you

Re: Chart with a conditional sum

Tue, 24 Mar 2015 11:58:09 GMT

Maybe like this, you could use comma and use all the values

SUM({<ACC_TYPE_DESCRIPTION ={'At Bank','Cash Pooling'}>}BALANCE_CTV2)

amay

Re: Chart with a conditional sum

robert_mika — Tue, 24 Mar 2015 12:00:18 GMT

=SUM({<ACC_TYPE_DESCRIPTION ={'At Bank','Cash Pooling','Restricted','Short Term Investment'}>}BALANCE_CTV2)

Re: Chart with a conditional sum

Anonymous — Tue, 24 Mar 2015 12:05:22 GMT

Commas are used for this functionality in set analysis.
=SUM({<ACC_TYPE_DESCRIPTION ={'At Bank','Cash Pooling','Restricted','Short Term Investment'}>}BALANCE_CTV2)

You could also use:

=SUM({<ACC_TYPE_DESCRIPTION -={'Committed','Credit Facilities Drawn'}>}BALANCE_CTV2)

The '-' (minus) sign is used for exclusion.

Re: Chart with a conditional sum

Tue, 24 Mar 2015 12:12:28 GMT

Hi,

Try this expression.

SUM({<ACC_TYPE_DESCRIPTION ={'At Bank','Cash Pooling','Restricted','Short Term Investment'}>} BALANCE_CTV2)

Re: Chart with a conditional sum

sunny_talwar — Tue, 24 Mar 2015 12:14:24 GMT

You can use this as Utkarsh pointed out

=SUM({<ACC_TYPE_DESCRIPTION -={'Committed','Credit Facilities Drawn'}>}BALANCE_CTV2)

another variation of the same thing would be to use e() like this:

=SUM({<ACC_TYPE_DESCRIPTION =e({<ACC_TYPE_DESCRIPTION = {'Committed','Credit Facilities Drawn'}>})>}BALANCE_CTV2) here the e is telling the expression to exclude the ACC_TYPE_DESCRIPTION where it is Committed or Credit Facilities Drawn.

HTH

Best,

Sunny

Re: Chart with a conditional sum

Tue, 24 Mar 2015 12:15:31 GMT

Thank, It works perfectly!

Thanks a lot

Re: Chart with a conditional sum

Anonymous — Tue, 24 Mar 2015 12:17:56 GMT

Great!

Re: Chart with a conditional sum

sunny_talwar — Tue, 24 Mar 2015 12:19:25 GMT

Mark Utkarsh's answer as correct so that the thread could be closed.

Best,

Sunny

Re: Chart with a conditional sum

Tue, 24 Mar 2015 15:24:58 GMT

Dear all,

I've now an additional issue as my conditional SUM needs to operate on two different filed. In particular I need to sum combining this two condition

SUM({<myWeekday ={'Ven'}>}BALANCE_CTV2)

and

SUM({<ACC_TYPE_DESCRIPTION -={'Committed','Credit Facilities Drawn'}>}BALANCE_CTV2)

It means that the sum filed BALANCE_CTV2 must be only with the above two occurence.

Could you help with the sintax?

Thank you

Re: Chart with a conditional sum

Anonymous — Tue, 24 Mar 2015 16:15:06 GMT

SUM({<myWeekday ={'Ven'},ACC_TYPE_DESCRIPTION -={'Committed','Credit Facilities Drawn'}>}BALANCE_CTV2)

Re: Chart with a conditional sum

Tue, 24 Mar 2015 16:46:43 GMT

Great thank you!