topic Re: Active Members Count in Water Cooler

Active Members Count

stevejones1 — Thu, 17 Jan 2019 03:21:36 GMT

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!!

Re: Active Members Count

zzyjordan — Thu, 17 Jan 2019 04:39:28 GMT

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;

Re: Active Members Count

jonathandienst — Thu, 17 Jan 2019 05:50:19 GMT

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;

Re: Active Members Count

stevejones1 — Mon, 21 Jan 2019 02:14:05 GMT

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

Re: Active Members Count

stevejones1 — Mon, 21 Jan 2019 02:21:04 GMT

what is the inner join based on ?

Re: Active Members Count

zzyjordan — Mon, 21 Jan 2019 02:43:40 GMT

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