Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gschramm
Contributor II
Contributor II

Counting with multiple condition

Hello,

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

IDTeam
1A1
1A2
1B2
2A1
2A2
2A1
3A1
3A3
3A2
4A1
4B1
4C2

 

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.

3 Solutions

Accepted Solutions
sunny_talwar

Another option

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

or 

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

 

View solution in original post

treysmithdev
Luminary Alumni
Luminary Alumni

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])

 

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

gschramm
Contributor II
Contributor II
Author

That what I just did, works 🙂

 

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

View solution in original post

9 Replies
martinpohl
Partner
Partner

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

treysmithdev
Luminary Alumni
Luminary Alumni

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

clipboard_image_0.png

Blog: WhereClause   Twitter: @treysmithdev
sunny_talwar

Another option

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

or 

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

 

View solution in original post

gschramm
Contributor II
Contributor II
Author

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
Luminary Alumni
Luminary Alumni

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])

 

Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

gschramm
Contributor II
Contributor II
Author

That what I just did, works 🙂

 

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

View solution in original post

gschramm
Contributor II
Contributor II
Author

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
Luminary Alumni
Luminary Alumni

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 
  ...;
Blog: WhereClause   Twitter: @treysmithdev
gschramm
Contributor II
Contributor II
Author

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?