topic Re: set expression in QlikView

set expression

upaliwije — Thu, 13 Aug 2015 10:06:41 GMT

I have following expression in my QV document and it is working fine

if(Sum({<DateType={PROVISION}>}PROV_AMOUNT)>0,count(DISTINCT{<DateType={PROVISION}>}CLAIM_NO))

( This count should be positive value) eg100

I want combine the following expression to the above expression obtain one result

if(Sum({<DateType={PROVISION}>}PROV_AMOUNT)<0,count(DISTINCT{<DateType={PROVISION}>}CLAIM_NO))

( This count should be negative value) eg -25

Final result should be 100-25=75

Pls help me to build the expression

Re: set expression

sunny_talwar — Thu, 13 Aug 2015 10:09:42 GMT

What id you do this:

=if(Sum({<DateType={PROVISION}>}PROV_AMOUNT)>0,count(DISTINCT{<DateType={PROVISION}>}CLAIM_NO)) - if(Sum({<DateType={PROVISION}>}PROV_AMOUNT)<0,count(DISTINCT{<DateType={PROVISION}>}CLAIM_NO))

Re: set expression

upaliwije — Thu, 13 Aug 2015 10:11:51 GMT

It does not return the expected results but show - as value

Re: set expression

sunny_talwar — Thu, 13 Aug 2015 10:13:19 GMT

If they work individually, I don't see why they won't work together in a single expression

Re: set expression

maxgro — Thu, 13 Aug 2015 10:14:35 GMT

or maybe

rangesum(yourfirstexpr, - yoursecondexpr)

Re: set expression

sunny_talwar — Thu, 13 Aug 2015 10:15:56 GMT

Great idea

Re: set expression

upaliwije — Thu, 13 Aug 2015 10:18:25 GMT

Can you please write the full expression

Re: set expression

upaliwije — Thu, 13 Aug 2015 10:26:02 GMT

Pls see whether what I have written is correct

=if(rangeSum({<DateType={PROVISION}>}PROV_AMOUNT)>0,count(DISTINCT{<DateType={PROVISION}>}CLAIM_NO)), -

{<DateType={PROVISION}>}PROV_AMOUNT)<0,count(DISTINCT{<DateType={PROVISION}>}CLAIM_NO)

Re: set expression

sasiparupudi1 — Thu, 13 Aug 2015 11:05:22 GMT

rangesum(if(Sum({<DateType={PROVISION}>}PROV_AMOUNT)>0,count(DISTINCT{<DateType={PROVISION}>}CLAIM_NO),0),-if(Sum({<DateType={PROVISION}>}PROV_AMOUNT)<0,count(DISTINCT{<DateType={PROVISION}>}CLAIM_NO),0))

Re: set expression

upaliwije — Thu, 13 Aug 2015 11:09:29 GMT

Thanks all