Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Difficult SET analysis case!


My task is next:

I use 3 columns: "ID_Transportation", "ID_User", "Status". 

I want to find only ID_User that match two Statuses (for example, Status_A  and Status_B). So my task is very similar to this: https://community.qlik.com/t5/New-to-Qlik-Sense/Using-P-with-multiple-criteria/m-p/21057. But its more difficult: ID_User should match two statuses which correspond to single transportation! I marked with red exact situation I trying to find in mini table below:

ID_Transportation | ID_User | Status

1                                             1           Status_A 

1                                             1           Status_С

1                                             1           Status_B 

1                                             2           Status_B 

2                                             2           Status_A 

2                                            and so on...


How I trying to solve this problem:

I trying to count for every ID_Transportation those Users ID which correspond to required statuses (Status_A and Status_B). Then I want to sort table and exclude zeros. And i will have only cases when rule is broken.

I use next formula:

count ( aggr (

{$<[ID_User]=p({<[Status]={"Status_A"}>})*p({<[Status]={"Status_B"}>})>} distinct [ID_User]

,  [ID_Transportation]

) )


What is my problem:

If I select single ID_Transportation my formula counts times when rule was broken correctly.

But when I select several transportation, formula returns overestimated value. I believe, this is because formula counts all ID_User ever matched both statuses, even if that ID_User doesn`t match them for this particular ID_Transportation. 

So how should i modify formula to met my goal??

Will be very grateful for help! Thank you!

Labels (2)
3 Replies
Contributor II
Contributor II

Re: Difficult SET analysis case!

It is still important to me. 

I am literally broke my mind with this task in last couple days. So if anyone could give me advice, I would be incredibly happy.


Re: Difficult SET analysis case!

Create a new field in the script like this

ID_Transportation & '|' & ID_User as ID_Key

and then use this

Count({$<[ID_Key] = P({<[Status] = {"Status_A"}>}) * P({<[Status] = {"Status_B"}>})>} DISTINCT [ID_User])


Contributor II
Contributor II

Re: Difficult SET analysis case!

Thank you for reply.

Probably creating a new field assumes using Data Loading Editor, but I never done it before and dont know how. Maybe you can send me link to some tutorial, so I learn faster? My data loaded from OLE DB, I converted it in SSMS.