Why two if statements with a variable condition need sa aggr() to work out?

[Applicable88]

Hello,

I always wondering why two conditions outcomes with a variable needs a aggr() to work. 

I try to make an easy example.I have following case:

Given is may variable vFormula='1'

if(if(vFormula='1', count({$<Type='Red'>}, count({$<Type='green'>}),Ordernumber)

So basically when variable vFormula is '1' show me the ordernumbers where type is red, and if its '0' then show me those ordernumbers where type is green. I find it makes sense but seemingly no returns. 

Only if if put in additional aggr() and a boolean query like >0 it seems to work right for me:

aggr(if(if(vFormula='1', count({$<Type='Red'>}, count({$<Type='green'>}),Ordernumber)>0), Ordernumber)

Why the first form wouldn't produce results like the last one?

Thank you very much in advance. 

Best. 

[Anil_Babu_Samineni]

For me your conditional syntax is wrong. Perhaps you could try this way

If(vFormula='1', count({$<Type={'Red'}>} Ordernumber), count({$<Type={'green'}>} Ordernumber)) // This condition will work when you have Only 1 and 0

If(vFormula='1', count({$<Type={'Red'}>} Ordernumber), If(vFormula='0', count({$<Type={'green'}>} Ordernumber))) // This condition will work when you have 0,1,2,3,...

Pick(Match($(vFormula), '1', '0'),  count({$<Type={'Red'}>} Ordernumber), count({$<Type={'green'}>} Ordernumber)) // But, I would go this way to make more efficient

[Applicable88]

Hello @Anil_Babu_Samineni , 

I found the mistake in my syntax. I misplaced the '>0' value. 

Old:

aggr(if(if(vFormula='1', count({$<Type='Red'>}, count({$<Type='green'>}),Ordernumber)>0), Ordernumber)

Of course thats the right one, I made a typo:

aggr(if(if(vFormula='1', count({$<Type='Red'>}, count({$<Type='green'>})>0,Ordernumber)), Ordernumber)

That corrected one give me the dimension with the given condition. And back to my question:

When I take away the aggr() why this gives me no returns?I get null values only but no error:

if(if(vFormula='1', count({$<Type='Red'>}, count({$<Type='green'>})>0,Ordernumber)

The syntax according to qlik is correct. The first if statement inside should differentiate between vFormula 1 and 0. The outer If shoud say whatever inside has a count than give me the ordernumber of it. Do you have an idea why it only works after aggregate once again to the dimension "ordernumber"?

Thanks for the tip with pick/match. I will look it up.

Best. 

[Anil_Babu_Samineni · Accepted solution]

Could be because you are using this as dimenion? If it is dimension Qlik should understand the functionlity of Aggregare of dimension on what you are preferring?

[Applicable88]

@Anil_Babu_Samineni thank you! you are right. the double if construct works, but only as measure not as dimension. But on the first look it returns like if its a dimension. 

Thank you very much.