Expired license count for selected Period

[girish2195]

Hi All,

I have license data of an inspector in which Inspector_ID, Activation_date, Expiry_Date and other fields. Once the license is expired, they may issue new license for the same inspector. So the Inspector_IDs are not unique. How do i get to know the number of inspectors whose license is expired for selected period in qlik sense text object.

Eg:

ID  ActDate    ExpDate

1   1 Jan 18    3 Mar 18

2   2 Jan 18   10 Mar 18

3   4 Apr 18   10 Dec 18

1   4 Mar 18    12 Dec 18

if the selected period is Jun 2018, Result should be 1 (ID = 2)

Please do help asap.

Thanks,

Girish.

[Quy_Nguyen]

It will be a little bit complex. The ideal is create the date field matches with every date range from your data.

Here is the code. I also attached the app in case you want to see the script and some expressions to show the active/expired account. !

Temp:
Load * INLINE [
ID, ActDate , ExpDate
1 ,1 Jan 18,3 Mar 18
2 ,2 Jan 18,10 Mar 18
3 ,4 Apr 18,10 Dec 18
1 ,4 Mar 18,12 Dec 18
4 ,4 July 18,12 Dec 18
];

//Format data and add flag
NoConcatenate
Data:
Load *,
 1 As ActiveFlag,
 ActDate&'_'&ExpDate As KeyRange;
Load ID, 
 Date(Date#( ActDate, 'D MMM YY')) As ActDate,
     Date(Date#( ExpDate, 'D MMM YY')) As ExpDate
Resident Temp;
Drop Table Temp;

// Loop for creating date through date range
TempCal:
Load Date,
 MonthName(Date) As MonthName;
Load 
 Date(StartDate + IterNo() -1 ) AS Date While (StartDate + IterNo() - 1) <= Num(EndDate);
Load Min(ActDate) As StartDate,
 Max(ExpDate) As EndDate
Resident Data;
// Match date with range
Inner Join
IntervalMatch(Date)
Load
 ActDate, ExpDate
Resident Data;

//Final Calendar
Cal:
Load 
 Date,
    MonthName,
    ActDate&'_'&ExpDate As KeyRange
Resident TempCal;
Drop Table TempCal;