Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
sully123
Contributor II
Contributor II

New customers by day in a given location

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 🙂 

Labels (1)
1 Solution

Accepted Solutions
MarcoWedel

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;

 

 

View solution in original post

5 Replies
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
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

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