topic Monthly Rolling Cumulative Total by Status - Help Please! in QlikView

Monthly Rolling Cumulative Total by Status - Help Please!

Anonymous — Fri, 05 Dec 2014 20:56:16 GMT

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.

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

MK_QSL — Fri, 05 Dec 2014 23:40:02 GMT

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

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!

Anonymous — Mon, 08 Dec 2014 16:57:41 GMT

Hello Manish --

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

Thank you!