Measure with if

[jacek27031]

Hi,

I have a measure with unit discount:

[STI Act]
*
([Cprice] - ([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))

Now I need to use sum function only if the measure is > 0. 

Will you help me with this? 

Thanks in advance!

[Or]

Sum(RangeMax(YourExpressionHere,0)) will prevent the sum from taking in any values that are smaller than 0.

[jacek27031]

hmm..., sth is wrong with my code:

Sum(RangeMax(sum([STI Act]
*
([Cprice] - ([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))),0))

[Or · Accepted solution]

You can't sum a sum, as is explained in the error message (at least, without using aggr()). Based on what you described, you should be using something like:

Sum(RangeMax(([STI Act]
*
([Cprice] - ([CDF Calc] / 1.23 / [ER Plan] * (1 - T2_GP) * (1 - T1_GP)))),0))

Since you only want rows that are above zero to be summed.

If you need an internal sum, you'll need to use aggr() and include the required dimensions.

[jacek27031]

Thanks so much, seems to be working now. 

And if I wanted to sum up the measures only <0, then Sum(RangeMin( ?

[Or]

Pretty much, yeah. RangeMin and RangeMax just pick out the smallest or biggest number from the ones they're given. By using e.g. RangeMax(YourExpression,0) you're just telling Qlik to pick out either your expression or 0, whichever is bigger. This effectively means only taking positive values for your expression because if the value is negative, 0 will be bigger.