Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Another option
=Count(DISTINCT {<ID = P({<Team = {"A*"}>})*E({<Team -= {"A*"}>})>} ID)
or
=Count(DISTINCT {<ID = P({<Team = {"A*"}>})-P({<Team -= {"A*"}>})>} ID)
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])
That what I just did, works 🙂
The numbers look odd, but that something that I need to check in the data. Thanks a lot!
count({<Team={"A*"}>}distinct ID) - count({<Team={"B*","C*"}>}distinct ID)
Another option
=Count(DISTINCT {<ID = P({<Team = {"A*"}>})*E({<Team -= {"A*"}>})>} ID)
or
=Count(DISTINCT {<ID = P({<Team = {"A*"}>})-P({<Team -= {"A*"}>})>} ID)
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
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])
That what I just did, works 🙂
The numbers look odd, but that something that I need to check in the data. Thanks a lot!
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)
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
...;
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?