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

      Resident GENTOO_WELLBEING_REFERRAL

      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.