Monthly Rolling Cumulative Total by Status - Help Please!

[Anonymous]

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 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

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

Month	Open Issues
January - 2014	5
February - 2014	6
March - 2014	8

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.

[MK_QSL · Accepted solution]

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()))

[Anonymous]

Hello Manish --

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

Thank you!