Counting with multiple condition

[gschramm]

Hello,

I can't figure this one out. Simplified version of the table I'm processing is: ID, Team

ID	Team
1	A1
1	A2
1	B2
2	A1
2	A2
2	A1
3	A1
3	A3
3	A2
4	A1
4	B1
4	C2

Consider these tickets and team assignments. Everying "A*" is the same department (the number indicates a subteam).

I'm trying to calculate the count of IDs that were handled with department A without ever going to a different group.

In the example above the result would be 2 (ID 2 and 3)

I got this working in the SQL DB (subquery),  but I need it Qlik Sense as a formula for a KPI. 

Help is much appreciated.

[martinpohl]

count({<Team={"A*"}>}distinct ID) - count({<Team={"B*","C*"}>}distinct ID)

[treysmithdev]

=Count(Distinct {<ID = P({<ID=E({<Team-={"A*"}>})>}) >}ID)

[sunny_talwar · Accepted solution]

Another option

=Count(DISTINCT {<ID = P({<Team = {"A*"}>})*E({<Team -= {"A*"}>})>} ID)

or 

=Count(DISTINCT {<ID = P({<Team = {"A*"}>})-P({<Team -= {"A*"}>})>} ID)

[gschramm]

Thanks for quick replies.

I tried both, the P/E version seems promising, but I can't get it working with a ValueList.

Unfortunately I'm not allowed to share the real raw data here. The team names don't follow a simple schema, it's really a bunch of names, the formula would be similar to this:

Count(Distinct {<[`Ticket_ID`] = P({<[`Ticket_ID`]=E({<Group_Name=valueList('1_xyz','1_abc','1_zyx','1_wer','1_qwe','2_qwe','fgh_ijl','1_dum','1_dum_tata')>})>})>}[`Ticket_ID`])

If I use Group_Name='1_xyz' the editor accepts the formula, with ValueList it doesn't

[treysmithdev · Accepted solution]

Try this:

Count(Distinct {<[Ticket_ID] = P({<[Ticket_ID]=E({<Group_Name={'1_xyz','1_abc','1_zyx','1_wer','1_qwe','2_qwe','fgh_ijl','1_dum','1_dum_tata'}>})>})>}[Ticket_ID])

[gschramm · Accepted solution]

That what I just did, works 🙂

The numbers look odd, but that something that I need to check in the data. Thanks a lot!

[gschramm]

Hopefully the last question about this.

I need to count this over time, the time information is a 2nd table which I can't join during load time (too much data).

The other table has lots of detail (including timestampes), the key between the two is the ID.

So I need to count the "ID_Details" that matches the resultset (ID_Details = ID)

[treysmithdev]

Associate the tables so that they are linked together in the data model.

Do this by renaming ID_Details to ID.

Details:
Load
    ID_Details as ID,
    TimestampField,
    ...
From 
  ...;

[gschramm]

That's what I can't do because of too much data, these tables have millions of lines and I'm limited to the Desktop Edition.

Is there any other option?