Count Distinct Customer Visits Without Showing Customers in Card

Reply
Highlighted
White Belt

Count Distinct Customer Visits Without Showing Customers in Card

I am needing some help with a project I am working on where I need to report on how many customers visited in a given time frame.

 

Attached is sample/dummy data of the dataset I am trying to analyze.

 

Requirements:

1) Use Visit_Date as a filter (date range)

2) Report how many distinct Customer_ID visited during the filtered Visit_Date range

 

Expected output:

For 1/1/2020-1/10/2020

Visit_Count_Bucket            Count_of_Customer_ID

1                                         2

2                                         1

3                                         1

5                                         1

 

Current Visit_Count_Bucket code that isn't working UNLESS I bring in Customer_ID into the card

Case when COUNT(DISTINCT `Customer_ID`, `Visit_Date`)  = 1 THEN '1'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) = 2 THEN '2'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) = 3 THEN '3'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) = 4 THEN '4'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) = 5 THEN '5'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) >= 6 THEN '6+'
     when COUNT(DISTINCT `Customer_ID`, `Visit_Date`) <=0 THEN 'No Visits'
     end

Accepted Solutions
Highlighted
Red Belt

Re: Count Distinct Customer Visits Without Showing Customers in Card

@JeffDaySJP  i hate to say it, but I don't think you're going to get this one across the line without pre-aggregating your data.

 

with the new Data Views beta, you'll be able to generate the aggregation in a VIEW, a la.

CREATE VIEW new_domo_dataset as 

SELECT

count( distinct customer, date) as visit_count,

customer,

date

from table

GROUP BY

customer, date

 

AND then use new_domo_dataset as the input for a card.

 

ask your CSM about access to the data views beta.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post


All Replies
Highlighted
Red Belt

Re: Count Distinct Customer Visits Without Showing Customers in Card

@JeffDaySJP  i hate to say it, but I don't think you're going to get this one across the line without pre-aggregating your data.

 

with the new Data Views beta, you'll be able to generate the aggregation in a VIEW, a la.

CREATE VIEW new_domo_dataset as 

SELECT

count( distinct customer, date) as visit_count,

customer,

date

from table

GROUP BY

customer, date

 

AND then use new_domo_dataset as the input for a card.

 

ask your CSM about access to the data views beta.


Jae Wilson
Check out my Domo Training YouTube Channel

**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

Announcements
Coming Soon: Weekly Virtual User Meet-ups to answer all your questions! Stay tuned for more details to come this week.