How to group the data based on condition

[Bala_s]

Hi Experts,

I need your help to get solution for my below query. 

In the below attached screenshot, I required to identify/count the unique ID's where Readiness is not equal to R0. In the attached example, the answer will be 2 count (60019249,60020272) which doesn't have R0. If i use set analysis then the 3rd ID also counted which is wrong. Also, i tried with Aggr function for ID. But no expected result. 

Request your help at the earliest pls..

Happy New Year 💐

Thanks,Bala

[Andrei_Cusnir · Accepted solution]

Hello,

Can you please try the following approach and let me know if it works for your use case scenario?

1. I have recreated the same dataset in Qlik Sense:

2. Using the expression: =Count( {<Readiness={"R0", "R1", "R2", "R2+"}>} DISTINCT ID ) will give you the count 3, as you are counting all the unique IDs where Readiness is not NULL.

3. Using the expression: =Count( {<Readiness={"R0"}>} DISTINCT ID) will give you the count 1, as you are counting only the unique IDs where Readiness is R0

4. Combining both expressions into one: =Count( {<Readiness={"R0", "R1", "R2", "R2+"}>} DISTINCT ID ) - Count( {<Readiness={"R0"}>} DISTINCT ID), will give you the count 2:

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, please mark it as accepted solution to give further visibility to other community members. 
 

[Bala_s]

Hi @Andrei_Cusnir ,

Thanks for the great help, It's working as expected..

Happy New Year 2022..

Thanks, Bala