Count of unique customers in Sales Table

[profilejamesbond]

Hi,

I have sales table and I want to count unique customer from that sales table.

If you see in the given below Sales Table there are customers that visit the stores on different dates.

For example: Customer A visited Store A for 4 times from 01.01.2022 to 01.01.2023 therefore it is active 4 times but it is unique customer only in 01.01.2022 beccause he visits first time store later on customer A visits the same store multiple times therefore he is not unique anymore from 01.02.2022 to 01.01.2023 because it is already counted unique in 01.01.2022. Same scenarios for other customers.

Later on I want to visualize these Customers by Store.

Dimension: Month-Year

Expression: Count of Unique Customers

Sales Table

Store	Customer	Visit Date	Status
101	A	01.01.2022	Active
101	A	01.02.2022	Active
101	A	01.03.2022	Active
101	A	01.01.2023	Active
102	B	01.12.2022	Active
102	B	01.01.2023	Active

[justISO]

Hi, so basically, as I understand, you want to have indicator on first time (date), when new customer visited store and this can be achieved something like this: when loading your [Sales Table], add 'order by Customer, Visit Date;' condition to order your customers, and as this indicator you can use something like 'if(Customer<>previous(Customer), 'unique', 'not') as unique_ind'. This simply mark first row for each customer as unique. But this approach is for marking data as first unique. By your post title, you just want to count unique customers, and this can be done by

Load:

count(DISTINCT Customer) as count

Resident [Sales Table];

[profilejamesbond]

Hi @justISO,

Yes, I want unique customer with these dimensions. Let's suppose, if the customer is unique in the previous month then it should be counted in the previous month. I need only 1 time count when the customer appeared first time and also dimension need to be there like Jan has 1 unique customer etc.

Thanks

[justISO]

So, as described previously, something like this should work:

sample:
load * inline [
Store, Customer, Visit Date, Status
101, A, 2022-01-01, Active
101, A, 2022-01-02, Active
101, A, 2022-01-03, Active
101, A, 2023-01-01, Active
102, B, 2022-01-12, Active
102, B, 2023-01-01, Active];

NoConcatenate
main:
load 
[Visit Date],
Store,
Customer,
Status,
if(Customer<>previous(Customer), 'unique', 'not') as unique_ind
resident sample
order by Customer, [Visit Date];

drop table sample;