Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Count IF for specific values in Qliksense

Im trying to come up with a KPI which would count only a certain field if the value of another field is >0 for the Max date .

For example this was the data set looks like ,  I need the KPI to show the count of part number for only 01/24/2020 where the BO PCS is >0

 

Any help is appreciated 🙂

 

Part NumberBO PCSRun Date
PA1778939393001/24/2020
Piasadasdasdad101/24/2020
Plasdasdadasd201/24/2020
Kiasdasdsadasd401/24/2020
JI91299299392701/10/2020
HU912319231923801/10/2020
UU1231231231901/10/2020
1 Solution

Accepted Solutions
Highlighted
Contributor II
Contributor II

Hi @jberna26 @Kush 

 

thank you so much for your help , I used the below expression and it worked 🙂

 

Count(DISTINCT {<[Run Date] = {"$(=Max([Run Date]))"}, [BO Pcs] = {">0"}>} [Part Number])

View solution in original post

7 Replies
Highlighted
Partner
Partner

Hi @Anoop91 ,

 

Not sure I understood, in any case, here goes nothing 🙂

If the result using your example table would be 3, then the formula would be something like

Count( {<[Part Number]={“=Sum({<[Run Date]={“=max([Run Date])”}>}[BO PCS])>0”}[Part Number])

 

This formula will not work like this, due to the double quotes, but if you put =max([Run Date]) inside a variable (or even the entire sum) and call that variable inside the set analysis it might work. 

Hope it helps 🙂

Cheers

Highlighted
Contributor II
Contributor II

Hello @jberna26 

 

Yes that is exactly what I need , the KPI value should be 3 . I tried using the formula you suggested but it says there is an error in the expression , Im too much of a newbie to diagnose what it might be 😞

 

error message says "Error in set modifier ad hoc element list ',' or ')' expected 

 

thank you for your help !

 

 

Highlighted
MVP
MVP

may be this

Count( {<[Run Date]={"$(=max([Run Date]))"},[Part Number]={“=[BO PCS]>0”}>}[Part Number])

Highlighted
Contributor II
Contributor II

Thanks for your reply , unfortunately it throws the same error message !

Highlighted
Partner
Partner

Hi @Anoop91 ,

 

Using @Kush suggestion, but with a tweek:

Count( {<[Run Date]={"$(=max([Run Date]))"},[Part Number]={“=Sum([BO PCS])>0”}>}[Part Number])

 

Again, not sure it would work.

 

Let us know 😉

Highlighted
MVP
MVP

please copy paste your expression here. May be you are doing some syntax error

Highlighted
Contributor II
Contributor II

Hi @jberna26 @Kush 

 

thank you so much for your help , I used the below expression and it worked 🙂

 

Count(DISTINCT {<[Run Date] = {"$(=Max([Run Date]))"}, [BO Pcs] = {">0"}>} [Part Number])

View solution in original post