Quarterly reporting - reporting last day activity

[Anonymous]

Hi all,

I am doing some quarterly reporting where I have to report on sums of activity for Elective Surgery reduction.  This is fine, but where I am getting in trouble is reporting a special type of activity on the last day of the month.

For example.  Over the 3 month period a patient may be suspended on the list due to ill health.  If they are Not suspended on the last day of the month I need to show them as Not Suspended.

The attachment shows a patient who was suspended off the waiting list for the first part of the July - September quarter.   If someone looked at my report for early August I would want them to see that this patient is Suspended.  However,  if they were to look at the report from end of August onward (where the patient is no longer suspended) I would want the count of suspended to = 0.

I've placed some comments in my attachment and hope that someone can help me out with this.

thank you.

John.

[sivarajs]

Check the file

[Anonymous]

Sivaraj,

Thank you for replying.  I tried something similiar (using a variable to get MAX(DATEValue)) but wasn't able to get it working.

Your solution works for the last day of the quarter, but what if you select a DATEValue range of 31/07/2012 - 15/08/2012?  All of these have the suspension flag set to 1 and the value of MAX(DATEValue) should now be 15/08/2012. 

Yet the count of Suspended is 0.

What I am trying to say is that the report needs to be accurate at any given point during the current quarter - or historically if the user selects an earlier month - Say July.

Thanks,

John.

[rwunderlich]

How about:

sum(aggr(FirstSortedValue(PeriopSuspendedFlag, -DATEValue), SourceIdentifier))

-Rob

http://robwunderlich.com

[Anonymous]

Rob,

Thank you.  This is quite ingenious and seemed at first to do the job.  However, I'm still getting an incorrect count of Suspended patients.

The new attachment now has two patients in it.  Both are suspended on the last day of September however, the pivot table only shows a count of 1 suspended.

Any ideas?

Kind Regards,

John.

[rwunderlich · Accepted solution]

I'm guilty of carelessness on this one. The additional dimensions should be included in the aggr:

SUM(aggr(FirstSortedValue(PeriopSuspendedFlag, -DATEValue-1), SourceIdentifier, FinancialYear, Quarter))

-Rob

[Anonymous]

ah.

thank you so much.

I hope that one day the mysteries of AGGR() will be clearer to me.