Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everybody,
I'm having difficulty creating a script that returns the amount of customers at a given month (first day of the month as reference would be fine).
Suppose the customer table would look something like:
Customers:
load * inline [
Name, Startdate, Enddate
Mike, 1-1-2019,
Johan, 1-10-2009, 1-1-2018
Marc, 1-12-2015, 1-1-2020
];
Example: for customer Marc the result should be somthing like:
2019-12: active
2020-01: active
2020-02: inactive (or null(), no record)
What's the best way to do this? I was able to create it with refence dates (daily), but I now want to create it with reference months, since in reality there are over 300.000 customers. As a result the date script is creating too many rows in the calendar table.
Any help appreciated!
Thanks,
Mike
Try this script
Customers:
LOAD Name,
Date(MonthStart(Startdate, IterNo()-1), 'YYYY-MM') as MonthYear
While MonthStart(Startdate, IterNo()-1) <= Enddate;
LOAD Name,
Date(Date#(Startdate, 'D-M-YYYY')) as Startdate,
Date(If(Len(Trim(Enddate)) = 0, Today(), Date#(Enddate, 'D-M-YYYY'))) as Enddate;
LOAD * INLINE [
Name, Startdate, Enddate
Mike, 1-1-2019
Johan, 1-10-2009, 1-1-2018
Marc, 1-12-2015, 1-1-2020
];
Marc would not have an entry for 2012-02 in the above example
What happens if the Month End is somewhere in the middle of the month? For example, if Marc's Enddate was 2020-02-15... would he have a record in 2020-02 or not?
Marc would then be counted as an active customer. So we’d give him a two weeks free ride
Try this script
Customers:
LOAD Name,
Date(MonthStart(Startdate, IterNo()-1), 'YYYY-MM') as MonthYear
While MonthStart(Startdate, IterNo()-1) <= Enddate;
LOAD Name,
Date(Date#(Startdate, 'D-M-YYYY')) as Startdate,
Date(If(Len(Trim(Enddate)) = 0, Today(), Date#(Enddate, 'D-M-YYYY'))) as Enddate;
LOAD * INLINE [
Name, Startdate, Enddate
Mike, 1-1-2019
Johan, 1-10-2009, 1-1-2018
Marc, 1-12-2015, 1-1-2020
];
Marc would not have an entry for 2012-02 in the above example
Thanks Sunny! Exactly what I was looking for. Great stuff 🙂