topic Re: Conditional Sum in QlikView

Conditional Sum

Fri, 27 Jun 2014 11:03:04 GMT

Hello

I have a requirement to add up the measure (for each row) only if they are +ve.

The underlying data for a catalog 1123CB is as shown below

At Qlikview level, I have to display Catalog and Totals only.

Totals for catalog 1123CB should be 87+185+146 = 418

I am using the following formula in Qlikview to achieve this -

if(sum(A) <0, 0, sum(A))) +(if(sum(B) <0,0, sum(B) )) +(if(sum(C) <0, 0, sum(C)))

But the above gives me total of 264 as it does a Sum of Measures A,B,C and then applies for the formula.

How can I achieve this, please advise ?

Gysbert_Wassenaar — Fri, 27 Jun 2014 11:12:58 GMT

sum(rangesum(rangemax(A,0),rangemax(B,0),rangemax(C,0)))

its_anandrjs — Fri, 27 Jun 2014 11:18:40 GMT

By this you are also get correct result

if(sum(A) < 0, 0, sum(A)) +

if(sum(B) < 0, 0, sum(B)) +

if(sum(C) < 0, 0, sum(C))

Note:- Instead you select Sum of rows from the properties but Rangesum is right way

See snap here:-

Hope this helps

Fri, 27 Jun 2014 11:31:53 GMT

Plese find attached application.

Fri, 27 Jun 2014 11:47:21 GMT

Sum of rows - doesnot make any difference.

Rangesum works !

CELAMBARASAN — Fri, 27 Jun 2014 11:47:59 GMT

Try this

=RangeMax(A,0)+RangeMax(B,0)+RangeMax(C,0)