Customer count at given month

[miklodepiklo]

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

[sunny_talwar]

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?

[miklodepiklo]

Marc would then be counted as an active customer. So we’d give him a two weeks free ride

[sunny_talwar · Accepted solution]

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

[miklodepiklo]

Thanks Sunny! Exactly what I was looking for. Great stuff 🙂