topic Re: Count ONLY if 2 named services happen on same date in App Development

Count ONLY if 2 named services happen on same date

mbeccaria — Tue, 22 Mar 2022 17:50:55 GMT

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

Re: Count ONLY if 2 named services happen on same date

chrismarlow — Tue, 22 Mar 2022 19:51:21 GMT

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.

Re: Count ONLY if 2 named services happen on same date

Or — Tue, 22 Mar 2022 20:53:05 GMT

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.

Re: Count ONLY if 2 named services happen on same date

mbeccaria — Wed, 23 Mar 2022 16:06:03 GMT

Pure gold. Thank you both!