2 Replies Latest reply: Dec 8, 2014 11:57 AM by Joe Hutchings RSS

    Monthly Rolling Cumulative Total by Status - Help Please!

    Joe Hutchings

      Hello --

       

      I'm banging my head against the wall trying to figure this out. I need to have a bar graph with a monthly rolling cumulative total of "open issues". The rolling total should count issues that were open in one month, but closed in another. Here's what my data looks like.

       

      Issue #Open DateClose Date
      11/1/20141/3/2014
      21/2/20142/1/2014
      31/4/20141/4/2014
      41/6/2014
      51/8/2014
      62/4/20142/5/2014
      72/5/2014
      82/6/2014
      92/7/20144/1/2014
      103/1/20144/1/2014
      113/2/20143/4/2014
      123/5/2014

       

      With the above example as my data set, I should have a chart that displays the following:

       

      MonthOpen Issues
      January - 2014

      5

      February - 20146
      March - 20148

       

      Essentially, I continue to count the issues that are open into the next month, subtracting the issues that are closed.

       

      I cannot for the life of me figure out how to do this.

        • Re: Monthly Rolling Cumulative Total by Status - Help Please!
          Manish Kachhia

          Use below script

           

          Temp:

          Load

            [Issue #] as IssueNO,

            Date(Date#([Open Date],'M/D/YYYY')) as OpenDate,

            Date(Date#([Close Date],'M/D/YYYY')) as CloseDate

          Inline

          [

            Issue #, Open Date, Close Date

            1, 1/1/2014, 1/3/2014

            2, 1/2/2014, 2/1/2014

            3, 1/4/2014, 1/4/2014

            4, 1/6/2014,

            5, 1/8/2014,

            6, 2/4/2014, 2/5/2014

            7, 2/5/2014,

            8, 2/6/2014,

            9, 2/7/2014, 4/1/2014

            10, 3/1/2014, 4/1/2014

            11, 3/2/2014, 3/4/2014

            12, 3/5/2014,

          ];

           

           

          Final:

          Load

            IssueNO,

          // OpenDate,

            Month(OpenDate) as Month,

            Month(OpenDate) as Month2,

            'Open' as Flag

          Resident Temp;

          Load

            IssueNO,

          // CloseDate,

            Month(CloseDate) as Month,

            Month(AddMonths(CloseDate,1)) as Month2,

            'Close' as Flag

          Resident Temp;

           

           

          Drop Table Temp;

           

          Now create a straight Table

          Dimension

          Month

          Expression

          RangeSum(Above(Count({<Flag = {'Open'}>}IssueNO),0,RowNo()))-RangeSum(Above(Count({<Flag = {'Close'}>}IssueNO),0,RowNo()))

           

           

          Or

          Dimension

          Month2

          Expression

          RangeSum(Above(Count({<Flag = {'Open'}>}IssueNO),0,RowNo()))-RangeSum(Above(Count({<Flag = {'Close'}>}IssueNO),0,RowNo()))

          • Re: Monthly Rolling Cumulative Total by Status - Help Please!
            Joe Hutchings

            Hello Manish --

             

            I tried this out and it does seem to do what I'm looking for.

             

            Thank you!