Using if condition in an expression

[#1]

Hi, I am new to Qlikview and when I try to use the if statement, it keeps bringing the value zero. Can anyone help with the below please.

I need the expression to count Organisation names (ignoring duplicates) where the AUM is greater than zero. This is what I wrote in the expression -

= if(new_currentaum>=0, count(distinct [Organisation Name]), 0)

Thanks

Jalps

[MayilVahanan]

Hi

Try like this

count({<new_currentaum =  {">=0"}>}distinct [Organisation Name])

[#3]

try:

= count(distinct if(new_currentaum>=0, [Organisation Name]))

[#4]

That works! So I dont need to use if statement in Qlikview it seems. Thanks very much

[MayilVahanan]

Hi

You can use if statement, but set analysis produces the result faster

[#6]

Jalpa,

in your case set analysis proposed by Mayil Vahanan Ramasamy is better

Of course there are also cases, where if is useful ....

regards

Darek

[#7]

Thanks again. Is it also possible to use set analysis or if statement on another expression rather than a dimension? For example, in the above condition I was trying to put the condition that if AUM is greater than zero then count Organisation Name. However, I now have an expression which subtotals the AUM. Can I use that expression in the if statement?

[hic]

Your expression cannot be evaluated properly. The reason is that you have a field reference that isn't aggregated.

    = if(new_currentaum>=0, count(distinct [Organisation Name]), 0)

QlikView evaluates this expression once for every dimensional value, so if there are several new_currentaum possible, this field reference will return NULL (It is evaluated as Only(new_currentaum)).

If you want it to be evaluated once per dimensional value, you should use an aggregation function, e.g. Max():

     =if(Max(new_currentaum)>=0, count(distinct [Organisation Name]), 0)

But if you want it to be evaluated once per record of raw data, you should put the If() inside the aggregation function, just as Dariusz Mielczarek suggests:

     =count(distinct if(new_currentaum>=0, [Organisation Name]))

Set analysis is a third way to do it. It is like making a selection - but just for this expression. Then you should do what Mayil Vahanan Ramasamy suggests:

     =count({<new_currentaum =  {">=0"}>}distinct [Organisation Name])

But Set Analysis is not like a condition: So you cannot toggle between different expressions.

HIC

[#9]

Thanks Hic. I see your point and used the max function but I still get the same issue - it counts the organisation where currentaum = 0.

[MayilVahanan]

Hi

Try like this

count({<new_currentaum =  {">0"}>}distinct [Organisation Name])