Count ONLY if 2 named services happen on same date

[mbeccaria]

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

[chrismarlow]

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 · Accepted solution]

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]

Pure gold. Thank you both!