Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Open cases as of date?

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!

5 Replies
muthukumar77
Partner - Creator III
Partner - Creator III

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,    

Muthukumar Pandiyan
MarcoWedel

Hi,

maybe one solution could be:

QlikCommunity_Thread_252806_Pic1.JPG

QlikCommunity_Thread_252806_Pic2.JPG

QlikCommunity_Thread_252806_Pic3.JPG

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

Not applicable
Author

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

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.