Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
Try like this
count({<new_currentaum = {">=0"}>}distinct [Organisation Name])
try:
= count(distinct if(new_currentaum>=0, [Organisation Name]))
That works! So I dont need to use if statement in Qlikview it seems. Thanks very much
Hi
You can use if statement, but set analysis produces the result faster
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
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?
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
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.
Hi
Try like this
count({<new_currentaum = {">0"}>}distinct [Organisation Name])