Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Average Set Analysis

Each row of data includes appointment data, Patiend data, Reason Data.

Can anyone tell me how I can work out the average appointments per patient only for reason1.

i.e.

Appt              Patient              Reason

Appt1              Pat1                 Reason1

Appt2              Pat2                 Reason2

Appt3              Pat2                 Reason1

Appt4              Pat1                 Reason2

Appt5              Pat1                 Reason1

I need Set Analysis to show Reason1 only ......

Avg appointments per patient (by patient)

Pat1 = 2 Appts

Pat2 = 1 Appt

The figure I need to show in the dashboard is for 'Reason 1' only and should be shown as below (not broken down by patient):

Overall Avg = 1.5 Appts per patient

Any help appreciated.

Phil

Phil

6 Replies
anbu1984
Master III
Master III

='Avg:' & Count({<Reason={'Reason1'}>}Reason) /Count(DISTINCT Patient)

datanibbler
Champion
Champion

Hi Phil,

there are several options:

- Filter in the script for Reason1

- Create a field for user_selection on the GUI for Reason1 (you can pre-select it upon opening by using a
   document_level_trigger)

- First calculate the count of appointments per patient (so you can calculate the avg on that) and then use set_analysis to filter for Reason1:
    >> = avg({$<Reason_code = {'Reason1'}>} [count of appts]) <<

HTH

Best regards,

DataNibbler

MK_QSL
MVP
MVP

=AVG(Aggr(COUNT({<Reason = {'Reason1'}>}Appt),Patient))

or

=COUNT({<Reason = {'Reason1'}>}Appt)/COUNT(Distinct Patient)

Anonymous
Not applicable
Author

Nearly there but it is counting 0's in the average.  I.e.  if there is a patient id but no reason, it counts average 0.  Therefore when it gives overall it gives fig lowever than expected?

Any thoughts?

MK_QSL
MVP
MVP

=AVG(Reason = {'Reason1'}Aggr(COUNT({<Reason = {'Reason1'}>}Appt),Patient))

or

=COUNT({<Reason = {'Reason1'}>}Appt)/COUNT({<Reason = {'Reason1'}>}Distinct Patient)

anbu1984
Master III
Master III

You can use condition Reason={'Reason1'} while counting Patient as suggested by Manish