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.



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'

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴
    Answer ✓

    @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 


    count( distinct customer, date) as visit_count,



    from table


    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 ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.