Your formula looks right if you are only counting "existing customers" if they actually made a purchase within each date interval you are charting. For example, if you are showing the count of existing customers by month, you would only be counting customers that actually made a purchase in each month (that was at least two weeks after their initial purchase).
Please provide some more detail on "the results do not look correct" & we might better be able to help.
I think that's my issue, Steve. My results look like they're leaving out a fair number of our customers. I'd like to really just exclude customers that made their first purchase within two weeks of the charted date.
You probably need to solve this on the data side (maybe someone out there has a brighter idea about solving it with the existing data source). I've solved similar problems by creating a 2nd table with just dates, e.g.:
And doing a full outer join with your transactional data so that you have records for every Report Day, even when customers don't actually make a purchase on that day. Then you can use Tableau to determine whether to count a customer as an active customer based on their initial purchase date & the report day.
The resulting tables can get very large of course, but you will have a way to reconstruct historical customer counts.
I'd certainly rather not have to manipulate the datasource here if I don't have to. But I will if I do.
I'd really like to be able to do this for several fields. Customers over time, employees over time, etc. Is there really not a good way to do this?
As long as you are working with a data source that just includes Purchase Date, I don't see an easy way (or any way really) to count existing customers.
I think you need to start with your rules for what an existing customer is - is it a customer who made a purchase _any time_ in the past, or do customers eventually drop off? Do you have a concept of "active customer" - customers who have purchased within the last 12 months, for example.
Then build your data source to fit - if you want to graph historical dates, you either have to build the date table with a join that I described above, or build out a data warehouse that snapshots customers.
The date table approach is not difficult - it just means spending some time on the data side instead of on the Tableau side - probably necessary if you want to start viewing historicals.
Okay, I've got the date table set up and am joining that against our purchases table. How would I count the number of customers now? A running total of COUNTD(customer id) won't do it.
I'm presuming it'll be an LOD function, but I'm unsure how the date table makes this easier.