topic Open cases as of date? in QlikView

Open cases as of date?

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Open cases as of date?

muthukumar77 — Fri, 10 Mar 2017 06:14:39 GMT

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,

Re: Open cases as of date?

MarcoWedel — Sat, 11 Mar 2017 01:35:36 GMT

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

Re: Open cases as of date?

Mon, 13 Mar 2017 12:17:04 GMT

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?

Re: Open cases as of date?

MarcoWedel — Fri, 17 Mar 2017 22:44:04 GMT

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

Re: Open cases as of date?

johnw — Fri, 17 Mar 2017 23:51:05 GMT

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.