Monthly "Caseload" chart - counting records based on arbitrary date.

[anthonyeeles]

Hello

First time poster on the community, and I have searched for this, but not really seeing what I need. Unfortunately "Case" and "Load" are fairly common search terms on here

I need to prepare a chart which shows how many of our cases were open on the last day of each month - our monthly case load. Effectively we are counting the number of cases which were referred (opened) before the month end, and are either open, or closed after the monthend.

At the moment, I am autogenerating a calendar with dates in it using code elsewhere on this forum, and then a table chart with GenericMonth as the dimension and the following expression:  

=count(distinct if((isnull([Case Closed]) or [Case Closed]>MonthEnd(GenericMonth)) and [Referral Received]<=MonthEnd(GenericMonth),CaseRef,''))

This works, but the chart takes an age to calculate. I'm sure there must be a better way, but as I don't reallly have a dimension linked to each case itself, I'm not sure what else I can do on here.

I'm a bit of a newbie at set analysis but I was wondering about having a go with that instead - in general, is S.A quicker than a "count if"?

Thanks in advance

Ant

[Anonymous]

Hi,

   Create a variable   VarMonthEnd=MONTHEND(DATEFIELD)

and use a variable in exression like

=count(distinct {<[Case Closed]={'>$(VarMonthEnd)'},[Referral Received]={'<=$(VarMonthEnd)'}>}CaseRef)

Above expression will be use full for you.

Regards

[anthonyeeles]

Hi Shaik, thanks for coming back to me.

That doesn't seem to be working for me - the graph shows 100% for all months. Also, the set analysis doesn't look like it would include cases where there is no closure date (i.e. the case is still open)

I've since I last posted,I've also tried making sure there is a GenericMonthEnd field in my autogenerated calendar field to replace the monthend calculation above. This hasn't made life any faster!

Cheers

Anthony