Count IF for specific values in Qliksense

[Anoop91]

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 Number	BO PCS	Run Date
PA1778939393	0	01/24/2020
Piasadasdasdad	1	01/24/2020
Plasdasdadasd	2	01/24/2020
Kiasdasdsadasd	4	01/24/2020
JI91299299392	7	01/10/2020
HU912319231923	8	01/10/2020
UU1231231231	9	01/10/2020

[jberna26]

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

[Anoop91]

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 !

[Kushal_Chawda]

may be this

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

[Anoop91]

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

[jberna26]

Hi @Anoop91 ,

Using @Kushal_Chawda 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 😉

[Kushal_Chawda]

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

[Anoop91 · Accepted solution]

Hi @jberna26 @Kushal_Chawda 

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