Hello!
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!
