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!
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:
LOAD * INLINE [
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!!
Try something similar like this:
LOAD Date("QtrStartDate",'DD/MM/YYYY') as [QtrStart],Date(QtrEndDate,'DD/MM/YYYY') as [QtrEnd] INLINE [
Inner join (tmp)
load "Orgid",Date(startDt,'DD/MM/YYYY') as "startDt", Date(endDt,'DD/MM/YYYY') as "endDt" inline [
Where ("startDt" >="QtrStart" and "startDt"<"QtrEnd")
("endDt" >="QtrStart" and "endDt"<"QtrEnd")
("endDt" >="QtrEnd" and "startDt"<"QtrStart")
Drop Table tmp;
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;