2 Replies Latest reply: Feb 5, 2013 8:22 AM by Anthony Eeles RSS

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

    Anthony Eeles



      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