Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
mbeccaria
Contributor II
Contributor II

Count ONLY if 2 named services happen on same date

I'm in a healthcare field using Qlik sense and I want to be able to count how many clients receive both service A and service B on the same day. If they received them on different days, don't count it.

So, in the table below, Client X received both service A AND B on 1/1/2022. No other client received service A and B on the same day, so the result should be 1. I tried the code below and it wasn't working. Any help on how to approach this?

=count(
	{<[Service Name] = {"A","B"}>}
    aggr
    	(Count
            ([Service Date]),
            [Client ID],
            [Service Date],
            [Service Name]
       	)
    )
Date Client Service
1/1/2022 X A
1/1/2022 X B
1/3/2022 Y A
1/3/2022 Y C
1/4/2022 X A
1/5/2022 X B
1/6/2022 X A
1/7/2022 X B
Labels (1)
1 Solution

Accepted Solutions
Or
Champion
Champion

Expanding on that suggestion, I would suggest:

Count(distinct (aggr(if(count(distinct {<Service={'A','B'}>}Service)=2,Client),Date,Client))

Assuming I didn't mess up the logic (I can't syntax-check it), this has two advantages:

1) It won't count instances where a client got service A or B twice in the same day, but not the other type. If that's not possible, the inner distinct isn't necessary.

2) It will count the number of distinct clients meeting the criteria, not the number of dates where it happened. If I read the requirement correctly, that's the intent.

If I did screw up the syntax, hopefully this is a nudge in the right direction.

View solution in original post

3 Replies
chrismarlow
Specialist II
Specialist II

Hi,

Not sure about your exact field names, but how about something like;

sum(aggr(if(count({<Service={'A','B'}>}Service)=2,1,0),Date,Client))

Cheers,

Chris.

Or
Champion
Champion

Expanding on that suggestion, I would suggest:

Count(distinct (aggr(if(count(distinct {<Service={'A','B'}>}Service)=2,Client),Date,Client))

Assuming I didn't mess up the logic (I can't syntax-check it), this has two advantages:

1) It won't count instances where a client got service A or B twice in the same day, but not the other type. If that's not possible, the inner distinct isn't necessary.

2) It will count the number of distinct clients meeting the criteria, not the number of dates where it happened. If I read the requirement correctly, that's the intent.

If I did screw up the syntax, hopefully this is a nudge in the right direction.

mbeccaria
Contributor II
Contributor II
Author

Pure gold. Thank you both!