IF greater, else zero

[helge_jorg]

Qlik experts,

Hopefully someone can help me with an IF-statement or set-analysis.

I want to display calculations dependent on a record if >100, than count ID. The same for the following criteria - >50, <50 and <1.

My problem is that, if one record has >100, there is also caunt for ID for >50, <50 and <1. These should be set to zero. If there is a record with greater than 100, the rest should be zero.

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">100"}>} distinct [Product.Prod_ID])

[sunny_talwar · Accepted solution]

Something like this?

[helge_jorg]

Thanks sunny!

It’s almost perfect. I see that my calculations would not work for <1 due to the <50 will also include 0.4 values. Is there an expression for less than 50 and greater than 1 (<50 and >1)?

[sunny_talwar]

I think you may want to change < 50 to >=1

[helge_jorg]

Perfect!

Thanks for your help!

[helge_jorg]

Sunny,

I have one more question that hopefully you can help me with. Can these expressions not be used in KPI? I want the KPI to display the sum, like the table does at the top line.

[sunny_talwar]

For these to work in KPI, wrap the expression around with Sum(Aggr(...)) function

Sum(Aggr(YourExpression, Date))

[helge_jorg]

I don't get it to work. Cloud you please help me getting this right:

sum(Aggr(Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">100"}>} distinct [Product.Prod_ID]) +

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">50"}>} distinct [Product.Prod_ID]) = 0,

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={"<50"}>} distinct [Product.Prod_ID], Date), 0))

This is the expression used in the table:

If(Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">100"}>} distinct [Product.Prod_ID]) +

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">50"}>} distinct [Product.Prod_ID]) = 0,

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={"<50"}>} distinct [Product.Prod_ID]), 0)

[sunny_talwar]

For > 50... you will use this

Sum(Aggr(

If(Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">100"}>} distinct [Product.Prod_ID]) = 0,

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">50"}>} distinct [Product.Prod_ID]), 0)

, Date))

For >= 1... you will use this

Sum(Aggr(

If(Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">100"}>} distinct [Product.Prod_ID]) +

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">50"}>} distinct [Product.Prod_ID]) = 0,

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">=1"}>} distinct [Product.Prod_ID]), 0)

, Date))

and for < 1... you will use this

Sum(Aggr(

If(Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">100"}>} distinct [Product.Prod_ID]) +

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">50"}>} distinct [Product.Prod_ID]) +

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={">=1"}>} distinct [Product.Prod_ID]) = 0,

Count({< [Customer.ElementID]= {'RR'}, [Customer.Value]={"<1"}>} distinct [Product.Prod_ID]), 0)

, Date))