topic Re: Quarterly reporting - reporting last day activity in QlikView

Quarterly reporting - reporting last day activity

Anonymous — Mon, 01 Oct 2012 05:54:53 GMT

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.

Re: Quarterly reporting - reporting last day activity

sivarajs — Mon, 01 Oct 2012 06:07:12 GMT

Check the file

Re: Quarterly reporting - reporting last day activity

Anonymous — Mon, 01 Oct 2012 22:57:52 GMT

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.

Re: Quarterly reporting - reporting last day activity

rwunderlich — Tue, 02 Oct 2012 01:29:56 GMT

How about:

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

-Rob

http://robwunderlich.com

Re: Quarterly reporting - reporting last day activity

Anonymous — Tue, 02 Oct 2012 02:05:39 GMT

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.

Re: Quarterly reporting - reporting last day activity

rwunderlich — Tue, 02 Oct 2012 18:55:45 GMT

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

Re: Quarterly reporting - reporting last day activity

Anonymous — Tue, 02 Oct 2012 22:31:05 GMT

ah.

thank you so much.

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