Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
philmywallet
New Contributor III

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

Tags (3)
6 Replies
anbu1984
Honored Contributor III

Re: Average Set Analysis

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

datanibbler
Esteemed Contributor

Re: Average Set Analysis

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

MVP
MVP

Re: Average Set Analysis

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

or

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

philmywallet
New Contributor III

Re: Average Set Analysis

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?

MVP
MVP

Re: Average Set Analysis

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

or

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

anbu1984
Honored Contributor III

Re: Average Set Analysis

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

Community Browser