Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
helge_jorg
Creator
Creator

IF greater, else zero

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])

sampl.png

1 Solution

Accepted Solutions
sunny_talwar

Something like this?

Capture.PNG

View solution in original post

8 Replies
sunny_talwar

Something like this?

Capture.PNG

helge_jorg
Creator
Creator
Author

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
Creator
Creator
Author

Perfect!

Thanks for your help!

helge_jorg
Creator
Creator
Author

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.

snip_20181105221906.png

sunny_talwar

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

Sum(Aggr(YourExpression, Date))

helge_jorg
Creator
Creator
Author

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))