Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

phase2medical
New Contributor III

Monthly Rolling Cumulative Total by Status - Help Please!

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.

1 Solution

Accepted Solutions

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

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

2 Replies

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

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

phase2medical
New Contributor III

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

Hello Manish --

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

Thank you!

Community Browser