New customers by day in a given location

[sully123]

I have a data set similar to the below, where I need to find the number of new customers each day, in a given location.

Customer	Location	Date	Formula below*	What I want
Customer 1	BM 01	06/02/2022	1	1
Customer 2	BM 01	08/02/2022	1	1
Customer 3	BM 01	09/02/2022	1	1
Customer 3	BM 01	09/02/2022	-	-
Customer 4	BM 01	11/02/2022	1	1
Customer 4	BM 01	16/02/2022	1	-
Customer 3	BM 01	22/02/2022	1	-

*I get column 4 by using the following:     aggr(count (distinct Customer), Location, Date)

But it's counting for new customers already accounted for in that location.

I'm interested in the day in which the new customers arrive, which is why I am looking for what I have entered manually in column 5.

Please let me know if you can help 🙂 

[MarcoWedel]

you could create a flag in the script that marks the first date per customer and location and just filter on this flag in your expression.

[sully123]

Thank you, I can try this. 

However, It'd need to be first date per customer and location as I have multiple locations. Is this still possible? I haven't done that before. 

[MarcoWedel · Accepted solution]

you could try something like:

Left Join (YourTable)
LOAD Customer,
     Location,
     Date(Min(Date)) as Date,
     1 as IsFirstDate
Resident YourTable
Group By Customer, Location;

[sully123]

That's great thank you, it worked! I would need to highlight the customers visiting twice in one day but I believe I know how to do that part.

[MarcoWedel]

glad it worked.

Please close your thread by accepting a solution.

thanks