Open cases as of date?

[#1]

I have a seemly easy question but having difficulty making it work, perhaps over thinking it.

Looking to see how many cases were pending as of the end of the month. Let’s say I have 6 months’ worth of data (below). I am looking to make a graph for the last 6 months, and count how many items were pending (not closed) as of that month. Example: case created in Jan, and closed in Apr should count 1 in Jan, Feb, Mar but not in the other months.

Next step I want to link this into each day, so I can go back to a specific day to see how many cases were pending as of that day, even if it is already closed as of today.

LOAD * INLINE [
CASE_ID,   CREATED_DATE,  CLOSED_DATE
1,         1/10/2017,     4/10/2017
2,         1/15/2017,     1/30/2017
3,         2/10/2017,     5/10/2017
4,         3/10/2017,    
5,         4/10/2017,     5/10/2017
6,         4/15/2017,    
7,         5/10/2017,     5/15/2017
8,         5/15/2017,    
9,         5/10/2017,     5/15/2017
10,        6/10/2017,    
];

Thanks!

[muthukumar77]

Are you expecting like this?

1,         1/10/2017,     4/10/2017 3

2,         1/15/2017,     1/30/2017 0

3,         2/10/2017,     5/10/2017 3

4,         3/10/2017,    

5,         4/10/2017,     5/10/2017 1

6,         4/15/2017,    

7,         5/10/2017,     5/15/2017 0

8,         5/15/2017,    

9,         5/10/2017,     5/15/2017 0

10,        6/10/2017,    

[MarcoWedel]

Hi,

maybe one solution could be:

table1:
LOAD * INLINE [
    CASE_ID, CREATED_DATE, CLOSED_DATE
    1, 1/10/2017, 2/10/2017
    2, 1/15/2017, 1/30/2017
    3, 2/10/2017, 3/10/2017
    4, 2/10/2017
    5, 2/10/2017, 2/20/2017
    6, 2/15/2017
    7, 3/02/2017, 3/05/2017
    8, 2/15/2017
    9, 3/10/2017, 3/11/2017
    10, 3/10/2017
];

tabDateLink:
LOAD CASE_ID,
    Date(CREATED_DATE+IterNo()-1) as Date
Resident table1
While CREATED_DATE+IterNo()-1<= Alt(CLOSED_DATE, Today());

tabCalendar: 
LOAD *, 
    Day(Date) as Day, 
    WeekDay(Date) as WeekDay, 
    Week(Date) as Week, 
    WeekName(Date) as WeekName, 
    Month(Date) as Month, 
    MonthName(Date) as MonthName, 
    Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
    QuarterName(Date) as QuarterName, 
    Year(Date) as Year, 
    WeekYear(Date) as WeekYear,
    -(Date=Floor(MonthEnd(Date))) as IsMonthEnd;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Date) as MinDate, 
    Max(Date) as MaxDate 
Resident tabDateLink;

hope this helps

regards

Marco

[#4]

I checked the QVW and is what I wanted; however, plugged that bit of code into my data.... It loaded

158,727,582 lines! Will keep looking at it, but any ideas on how to do this without the 158.7mm new records?

[MarcoWedel]

Hi,

I guess the number of rows could be reduced e.g. using parts of the concept John Witherspoon described here:

Subroutine to Create Data Model for From/To Date Selection

hope this helps

regards

Marco

[johnw]

That's probably what I'd do - add an intermediate table to reduce the number of rows while still connecting every case to every date it is open.

But another approach that might work would be to create a table of activity dates where you have a +1 on the date a case is created, and a -1 on the day after the case is closed. Then create an AsOf table to link any date to that and all previous activity dates. Then just sum your counter. See attached.