topic Re: Average Set Analysis in QlikView

Average Set Analysis

Anonymous — Mon, 15 Dec 2014 09:46:17 GMT

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

Re: Average Set Analysis

anbu1984 — Mon, 15 Dec 2014 09:53:07 GMT

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

Re: Average Set Analysis

datanibbler — Mon, 15 Dec 2014 09:54:37 GMT

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

Re: Average Set Analysis

MK_QSL — Mon, 15 Dec 2014 09:54:53 GMT

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

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

Re: Average Set Analysis

Anonymous — Mon, 15 Dec 2014 10:27:55 GMT

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?

Re: Average Set Analysis

MK_QSL — Mon, 15 Dec 2014 10:31:31 GMT

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

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

Re: Average Set Analysis

anbu1984 — Mon, 15 Dec 2014 10:57:37 GMT

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