Percent of clients receiving services from multiple service providers, per hospital, for service codes

[sai_12]

Hello 

I am working with data where i need to find how many clients received multiple services per provider for specific service codes

In the below data we have 3 patients id, single hospital, multiple services and their codes

Now we need to find percentage of clients who received multiple services for item codes starting with main

which is main_12 or main_13

patient_number	hospital	service received	service code
1	abc	physio	main_12
1	abc	cardio	main_13
1	abc	general checkup	not_nec_11
1	abc	leg	not_nec_12
1	abc	skin	not_nec_13
2	abc	physio	main_12
2	abc	cardio	main_13
2	abc	general checkup	not_nec_11
2	abc	leg	not_nec_12
2	abc	skin	not_nec_13
3	abc	physio	main_12
3	abc	leg	not_nec_12
3	abc	skin	not_nec_13

So in this case we have 3 clients client 1 and 2 have received multiple services with "main"item codes and client 3 did not receive multiple 

Expecting output

hospital	total clients	valid criteria	%
abc	3	2	66%

I have tried aggr function with set analysis but was unable to get to the correct output any help is much appreciated 

Looking for solutions in frontend as we need to create a new sheet without any changes to exisitng published app

[JonnyPoole · Accepted solution]

One of the things that makes me quite proud of the Qlik associative engine is the command that is bestowed on the developer through flexible search expressions that can be applied in SET modifiers.

In this case, I believe you are interested in seeing the 

- # of patients

- that have had 3 distinct services at the same hospital

In Qlik speak... you need to count the distinct service code (to ensure its more than 3) but breaking that down by each unique pair of hospital and patient. You need an aggr() to capture the context of hospital and patient as follows:

aggr(count(Distinct [service code]),patient_number,hospital)>3

...and then you only want to see the patients that fit that criteria so you place it in a SET modifier on patient_number... and thats the context

{<patient_number={"=aggr(count(Distinct [service code]),patient_number,hospital)>3"}>} 

Next you are interested in the # of patients in this criteria so use a count

Count( distinct {<patient_number={"=aggr(count(Distinct [service code]),patient_number,hospital)>3"}>} patient_number)

But, you could place the set statement into a concat() to see a list of those patients too: 

Concat( distinct {<patient_number={"=aggr(count(Distinct [service code]),patient_number,hospital)>3"}>} patient_number,', ')