topic Re: Customer count at given month in App Development

Customer count at given month

miklodepiklo — Sat, 16 Nov 2024 19:02:54 GMT

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

Re: Customer count at given month

sunny_talwar — Tue, 25 Feb 2020 12:44:23 GMT

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?

Re: Customer count at given month

miklodepiklo — Tue, 25 Feb 2020 12:49:07 GMT

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

Re: Customer count at given month

sunny_talwar — Tue, 25 Feb 2020 13:01:01 GMT

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

Re: Customer count at given month

miklodepiklo — Tue, 25 Feb 2020 13:45:23 GMT

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