Aggregate days between multiple dates with overlaps and skips

[#1]

Hello All, Please forgive me, I'm new to Qlik, but not entirely new to BI.I am teaching myself Qlik by developing an app that I previously developed in Power Pivot. I have a recurring calculation that I need to complete in virtually every data set that I work with.  It's a human services application, so we're interested in the number of days in membership a client has in the system and the number of days enrolled in one program or another.

Client #	Program	Entry Date	Exit Date	Days in Program*
1	A	1/1/2014	1/1/2016	730
1	B	4/1/2015	6/18/2015	78
1	C	1/5/2016	null	252
1	B	1/15/2016	2/20/2016	36
2	A	4/9/2015	null	523
2	C	3/7/2014	2/9/16704	704

* If Exit Date is null, then I replaced with current date of 9/13/2016 for this sample set.

If I use a simple arithmetic solution of subtracting the Entry Date from the Exit Date, (which is what I've seen on how to calculate days between in Qlik) and then summing it all together, it artificially inflates the client's membership in the system, like client 1 would be 1096, and client 2 would be 1227, when they really should be:

Client	Membership in System
1	Total of 1/1/2014 to 1/1/2016 and then skip 1/2/2016 -1/4/2016 and add 1/5/2016 to present for 730+252 = 982
2	First Entry of 3/7/2014 until present = 921.

In Power Pivot, I used the calendar system and I built a table including all the possible historical dates and 20 years into the future.The table quite literally lists every day between 1/1/2000 and like 2050. I used a count function that counted the number of days (records) in the calendar table between the dates in the client record in the details table. It quite neatly rolled the aggregates up for each client, providing the count of days between all the dates of membership. This is often tied into funding, so artificial inflation of the numbers is greatly frowned upon. In the full application, I will be charting client membership across programs, also.

Is there a function built into Qlik do do this? Would this counting days in a calendar table be the best way to handle it in Qlik and can you get me started out in the right direction?

Thank you so much for your help.

Elizabeth

[sunny_talwar]

May be this:

Table:

LOAD [Client #],

    Program,

    [Entry Date],

    [Exit Date],

    Date(If([Exit Date] = 'null', Today()-1, [Exit Date]-1)) as Calc_Exit_Date,

    [Days in Program*];

LOAD * INLINE [

    Client #, Program, Entry Date, Exit Date, Days in Program*

    1, A, 1/1/2014, 1/1/2016, 730

    1, B, 4/1/2015, 6/18/2015, 78

    1, C, 1/5/2016, null, 252

    1, B, 1/15/2016, 2/20/2016, 36

    2, A, 4/9/2015, null, 523

    2, C, 3/7/2014, 2/9/2016, 704

];

MinMax:

LOAD Min([Entry Date]) as MinDate,

  Max(Calc_Exit_Date) as MaxDate

Resident Table;

LET vMin = Peek('MinDate');

LET vMax = Peek('MaxDate');

Calendar:

LOAD Date($(vMin) + IterNo() - 1) as Date

AutoGenerate 1

While $(vMin) + IterNo() - 1 <= $(vMax);

Left Join (Table)

IntervalMatch(Date)

LOAD [Entry Date],

  Calc_Exit_Date

Resident Table;

[#3]

Thanks, could you walk me through the logic, a bit, please.  I'm especially not sure how the min and max functions would apply - not that I don't understand min and max, but how is it handling skipped days in membership if it's pulling min entry and max exit?

In the real data set, I am likely to encounter poor data quality, a common issue would be one client would be enrolled in a program a dozen times on a single day, but that's one day in membership.

[sunny_talwar]

I essentially used IntervalMatch‌ concept to join your fact table to the master calendar. Do you already have a master calendar in your app, if you do then you don't need that min/max logic (because you already might have done that to create a master calendar). If you don't, then you need to create master calendar (Creating A Master Calendar) and perform intervalmatch.

HTH

Best,

Sunny