Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Active Members Count

Hi 

I have a table which has the Org Id, NewCustomerDate ,ServiceExpirationDate180, fields which shows when the Customer has been added(NewCustomerDate) and when the Customer service has expired(ServiceExpirationDate180), 

I need to create a report by Quarter start dates , on how many Customers where active between NewCustomerDate and ServiceExpirationDate180.

Here are the Quarter Start Dates:

ActiveQtr:
LOAD * INLINE [
CommonDate,RecCount
8/1/2017,1
11/1/2017,1
2/1/2018,1
5/1/2018,1
8/1/2018,1
11/1/2018,1
2/1/2019,1
];

The metric i want is count of active customers between start and end dates , for a specific quarter , please let me know how i can structure the data for this 

 

I tried using the Interval Match , attached the application , but for some reason reload just hangs before the Inner Join in the script , Please help!!

5 Replies
Highlighted
Creator II
Creator II

Re: Active Members Count

Hi, Steve

Try something similar like this:


tmp:
LOAD Date("QtrStartDate",'DD/MM/YYYY') as [QtrStart],Date(QtrEndDate,'DD/MM/YYYY') as [QtrEnd] INLINE [
QtrStartDate,QtrEndDate
01/08/2017,01/11/2017
01/11/2017,01/02/2017
01/02/2018,01/05/2018
01/05/2018,01/08/2018
01/08/2018,01/11/2018
01/11/2018,01/02/2019
01/02/2019,01/05/2019
];

Inner join (tmp)
load "Orgid",Date(startDt,'DD/MM/YYYY') as "startDt", Date(endDt,'DD/MM/YYYY') as "endDt" inline [
Orgid,startDt,endDt
1,08/11/2017,20/10/2018
2,01/01/2018,10/03/2019
3,10/03/2018,05/07/2018
];

NoConcatenate
ActiveQt:
Load *
Resident tmp
Where ("startDt" >="QtrStart" and "startDt"<"QtrEnd")
or
("endDt" >="QtrStart" and "endDt"<"QtrEnd")
or
("endDt" >="QtrEnd" and "startDt"<"QtrStart")
;
Drop Table tmp;

 

ZZ

Highlighted
MVP
MVP

Re: Active Members Count

The interval match was not set up correctly. Try this:

ActiveQtr: 
LOAD * INLINE [
    CommonDate,RecCount
    02/01/2017,1
    08/01/2017,1
    11/01/2017,1
    02/01/2018,1
    05/01/2018,1
    08/01/2018,1
    11/01/2018,1
    02/01/2019,1
];

Left Join (ActiveQtr)
IntervalMatch (CommonDate)
LOAD NewCustomerDate, ServiceExpirationDate180 
RESIDENT LatteData 
WHERE ServiceExpirationDate180 >= MakeDate(2018,1,2);

Left Join (ActiveQtr)
LOAD *
RESIDENT LatteData; 

Drop Table LatteData;
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Contributor III
Contributor III

Re: Active Members Count

Can you tell me on which column the inner join is done? i dont see acommon table

Highlighted
Contributor III
Contributor III

Re: Active Members Count

what is the inner join based on ? 

Highlighted
Creator II
Creator II

Re: Active Members Count

A inner join without a key means a similar as cross join which brings all the combination of two data sets. and then use the where clause to filter the cross join data.

ZZ