Pivoting on the count of patients

[Anonymous]

Hi All,

I have been asked to build a table that shows the number of patients who have had their surgery postponed, and how often.

for example, the table below shows that Hospital A had 50 patients who were postponed once, 35 were postponed twich and so on.

Number of postponements	1	2	3	4
Hospital A	50	35	15	5
Hospital B	70	40	25	10

This data could change depending on the date range selected.

my data currently sits in table format.

date	patient	Hospital
Jan-11	1001	Hospital A
Jan-11	1001	Hospital A
Feb-11	1001	Hospital A
Jan-11	1002	Hospital A
Feb-11	1002	Hospital A
Jan-11	1003	Hospital A

My question is this:  Keeping in mind that different date ranges can be selected by the user, How do I provide a count of number of patients postponed and pivot or split this by the count of how often?

thank you,

John.

[swuehl · Accepted solution]

John,

maybe like attached?

I used a data island for the Number of Postponed dates (top pivot table dimension), since I haven't succeeded using Valueloop() in this case.

So my script is just:

LOAD F1 as Date,

     patient,

     Hospital

FROM

[http://community.qlik.com/thread/45773?tstart=0]

(html, codepage is 1252, embedded labels, table is @3, filters(

Remove(Row, Pos(Top, 8))

));

LOAD * INLINE [

Num Postponed

1

2

3

4

5

];

And the pivot table has dimensions Hospital and [Num Postponed] and one expression:

=sum(aggr(if(count(Date)=[Num Postponed] ,1),Hospital,patient,[Num Postponed]))

If you select on different Dates / patients, the numbers seem to be correct to me.

Hope this helps,

Stefan

[Anonymous]

Stefan,

you are a genius!

thank you.

John.

[swuehl]

No, not really 😉

Actually, there is another, I believe more standard, way to approach such kind of histogram chart:

Don't create a data island, then create a pivot table chart with dimension Hospital and another calculated dimension

=aggr(count(Date),patient)

and as expression

=count(distinct patient)

This way your numbers of postponements are also dynamically calculated as calculated dimension.

As you see there are often sometime different approaches to achieve the same, each with its advantages and disadvantages.

Just keep on playing a bit around with the flexibility of QlikView.

Regards,

Stefan

[Anonymous]

Stefan,

Thanks for this. I tried this method but found the values of the pivot changed as I drilled down. Ie: selecting the number 4 in the column headers which shows the count of how many patients have been postponed 4 times, the expression value changes from 2 people to 1. Whereas your previous method does not change. When I check the underlying data I find that there is only 1 patient with 4 postponements, not 2. So the first method you suggested is correct.

Thank you again. This is something new I have learned.

Regards,

John.

[swuehl]

You're welcome.

But it's strange that the value changes... If you like, you can post a sample that demonstrates what you see, I'll take a look then.

Have fun with QlikView,

Stefan

[Anonymous]

Stefan,

this issue with the values changing has reared its head again. and I am stumped.  Do you mind if I supply the QV for a second look?

My example uses scrambled data and a reduced dataset.  But the example is in tact. 

On the screen (with RED title bar) is the table I am interested in.  It should show the number of patients who have been postponed once, twice etc.  Because the dataset is so small we don't see anyone postponed more than twice.  But the total of postponements in this table equals 20, where the lower table which itemises each postponement shows 21.

I have tried variations of the AGGR function to no avail.  I think it is related to the Postponement Reason being different for the same patient and that this may be counted as 1 instead of 2.  

Regards,

John.

Message was edited by: janderson New attachment without Section Access. sorry.

[swuehl]

I get 'Access denied to this QlikView document', do you have section access enabled?

[Anonymous]

Hi Stefan,

can you try my attachement again please?  I've removed the Section Access.

Regards,

John.

[swuehl]

John,

I can now open your app, but I don't see postponements equal 20 resp 21, I see larger numbers (97, 105, 106 etc), so I am not sure if I am looking at the right stuff.

You could try:

=sum(aggr(if(count({$<CancelledProcedureFlag={1},BookingChangeReason={0}>}MonthYear)=NumberPostponed ,1),HospitalName,BookingNumber,BookingCancelReason ,NumberPostponed))

And you need to consider that one table is showing the count of events per NumberPostponed, the other the number of postponed procedures, so one table will show e.g. 1 event for '2' NumberPostponed, the other 2 for the number of postponed procedures.

Regards,

Stefan