Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Pivoting on the count of patients

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 postponements1234

Hospital A

5035155
Hospital B70402510

This data could change depending on the date range selected.

my data currently sits in table format.

date
patientHospital
Jan-111001Hospital A
Jan-111001Hospital A
Feb-111001Hospital A
Jan-111002Hospital A
Feb-111002Hospital A
Jan-111003Hospital 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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

16 Replies
swuehl
MVP
MVP

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
Not applicable
Author

Stefan,

you are a genius!

thank you.

John.

swuehl
MVP
MVP

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
Not applicable
Author

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
MVP
MVP

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
Not applicable
Author

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
MVP
MVP

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

Anonymous
Not applicable
Author

Hi Stefan,

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

Regards,

John.

swuehl
MVP
MVP

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