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.

[Anonymous]

Stefan,

I'm sure I tried this but maybe I didn't - there were a few combinations.  but thanks,  this has corrected the total to 106.

Regards,

John.

[Anonymous]

Hi, its me again.  The above code gave me a correct answer with the reduced data set that I had at the time, but still comes up short when I reduce the number of filters.

the current attachment shows the dilemma.  The aggregated table that you helped me build has a total of 1910 postponements but in a separate table I count 1916.

if you could take one last look that would be great.

thanks,

john.

Try again.  sorry about that.  its default on all our files.

[swuehl]

I get 'Access denied' again...

[Anonymous]

Hi Stefan,

I've uploaded the qvw again.  would you mind having another glance at it?

thank you very much.

John.

[swuehl]

I have not fully understood the issue (mostly because I don't really know your data model), but it seems the problem arise from the fact that you essentially count MonthYear in your one table, and distinct WaitingListOfferID in the other.

I noticed that for some BookingNumbers, you have same DateValues on some Cancellations, but the DateValue or MonthYear only gets counted once.

If I change the expression in the first table to

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

then the numbers are consistent between the two tables.

Hope this make any sense to you.

Stefan

[Anonymous]

Stefan,

yes thank you that does help.  and it makes me think that maybe I'm growing too reliant on you and this community when I should be using my brain more.  I guess i doomed myself to failure by not fully understanding the Aggr function and how it was working although the logic you've provided isn't that hard to separate out.

I should have done the fault checking more carefully instead of having you do it for me.

Thank you.

John.

[swuehl]

No worries, John.

It's always a healthy experience to see the effect of not chosing the right field and qualifier to count, also for me.

If you get into performance problems using the distinct count on large volumes, you might want to have a look into the performance optimization chapter in the Manual, book III.

Regards,

Stefan