4 Replies Latest reply on Mar 28, 2016 3:23 PM by Derek Wong

# Count number of customers given start and end dates

Hi, I'd like to chart the number of customers we have. I'm given the dates of their first service with us and the date they leave is (if any). How can I approach this problem?

• ###### 1. Re: Count number of customers given start and end dates

If I'm understanding your issue correctly, you would need a date column that has every date in order to make this chart. Then, a calculated field that is keeping a running count of active clients. The logic there would be to add a client count every time start date < master date, and to subtract one every time end date < master date.

I've attached a simple table in Excel to show you what I mean. See column H for the running net count of clients. Hope this gets you moving in the right direction! If not, providing a packaged workbook would help provide a solution.

• ###### 2. Re: Count number of customers given start and end dates

For the running count, would I just create a Calculated Field that returns 1 if start date < master date and returns -1 if end date < master date and just use a running total table function on it?

• ###### 3. Re: Count number of customers given start and end dates

Nevermind, that wouldn't work. How could I keep a running count in a calculated field?

• ###### 4. Re: Count number of customers given start and end dates

It might be easier to understand them if you split it into two separate calculations.

1. A count of all clients. if start date < master date then 1, else 0

2. A count of all clients who left if end date < master date then 1 else 0

Then, you could have a third calculation that simply subtracts the two, so that you get [all clients - clients who left]