13 Replies Latest reply: Nov 16, 2015 3:11 AM by clive ketley RSS

    How to display number of open cases in a month

    clive ketley

      Hi All,
      I am trying (and failing) to create a graph that shows the number of open cases within any given month. The cases have an open date and some have a close date but this is only entered at the point of closing so in some cases this field is null. The cases can be open for a period of several months (so I would like to show a count of 1 for that case in each month it is open even if it is only 1 day). I have tried creating reference dates for the period that the case is open which provides a list of cases open on each day. (using hic Creating Reference Dates for Intervals, That almost worked and I just needed to tweak it to use the Today function if there was no end date) When I try to display this as a monthly calendar it accumulates the total, see graphs below. I am expecting to see around 180 cases open in any particular month.

      ScreenShot 11-10-15 at 10.49.47 AM.png


      Code to create above table and master calendar


      load CASE_ID,

      Date(REFERRALDATE +IterNo()-1) as ReferenceDate


      While IterNo() <=if(isnull(END_SUPPORT_DATE),Today(),END_SUPPORT_DATE) - REFERRALDATE +1;

      [Cases Open Calendar]:

      LOAD ReferenceDate AS ReferenceDate,

      trim(date(date(ReferenceDate,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY ') )AS [ReferenceDate.Date],

      Month(ReferenceDate) As [ReferenceDate.Month],

      Year(ReferenceDate) As [ReferenceDate.Year]

      RESIDENT [Cases_x_Dates];




      Dimension used in graph is ReferenceDate.Month and Expression is Count(distinct CASE_ID)


      Any ideas or pointers would be appreciated as I do not know if I am even using the correct methodology.