distinct count not working

Hi all - 

 

I just want to know why the distinct count in beast mode is not working..

 

in the screenshot, I have a count distinct on the resource names, but there are still duplicates appearing and being recognized as distinct.

 

 

I am using this formula

COUNT(DISTINCT CONCAT(trim(`Test Taker: Resource Name`)))

any thoughts?

Comments

  • Ashleigh
    Ashleigh Florida 🟢

    Are you sorting the data by anything? If you sort it by the corrected Test Started then it is not going to count them as distinct.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🔴

    Try removing the date column on the left. Since those dates have different timestamps, they are making the row unique. If you just have the person's name as a column and then the count of the person's name, you should see a proper count.

  • we actually need the date as we use this as the basis for the graph when we separate them by month.

  • I am not sorting it with anything. 

  • @WorldWarHulk 

    you need to remove/change the date/time first column

     

    if you need a distinct count by day then the first column should be  Date(`Corrected Test Started`)

     

    if you need a distinct count by month then the first column should be your  'Year-Month' like

    Concat(Year(`Corrected Test Started`), '-', Month(`Corrected Test Started`))

  • Adding on to @Godiepi ...

     

    If you want a distinct count and need to display the first date the name was in the data, then change the agregate on the first column (the date column) to be MIN

     

    If you are more interested in the most recent date, then use MAX.  

     

    The reason you are seeing multiple "distinct" names is because the card is looking at the number of distinct names for each row in your table.  Since the Date/Time field is in the table (with no agregation), it is counting how many names match that date/time.

  • I changed the aggregate on the date column to MIN and the duplicates got worse.

  • Can you take a screenshot of your entire analyzer screen?  it may help to see what else might be going on.

     

    It may also help to get a few sample rows of data with the same schema as your data set to help troubleshoot.

  • here you go

  • here you go

     

     

     

     

     

  • here you go.

     

    for the screenshot on data 4...

    basically we have 4 records there based on the resource name, and the 5th one is a duplicate.

     

     

  • Ashleigh
    Ashleigh Florida 🟢

    You are sorting by MIN Test Started which will cause dups to show up. 

  • I'm not sure why you are getting a different result1.png

     

  • Also, it looks like when you drill in, you are using "corrected test date" in your table.  This could be part of the issue.  You changed your graph type on me from your initial post ?

    3.png

     

    4.png

     

  • it needs to be in a bar graph.. I just showed the table to show the duplicates being counted as distinct.

  • also, when I did the MIN in the corrected date in a table, It showed far less data now... a lot less...

  • In this case... remove the MIN sorting. 

     

    You will still end up with duplicates anytime the same user takes a test in multiple months.  The calculation will be counting the number of unique users that started a test in a certain month.  The overall summary number should still be accurate though.

  • yes. but those duplicate names shouldnt be counted anymore.

     

    per row, we will have different dates but we can have the same names. is there a way for domo to think that even though its different dates, since its the same name its a duplicate? 

  • Not if you are plotting it by month.

     

    The summary number, if you are using the distinct count funciton in your first post, should still give the correct overall number of distinct names.

     

    To demonstrate, I duplicated the 5 rows of data you provided twice and changed the month to be Feb and March.

     

    Here is the output.  You can see that the summary number is still four even though you can count 12 distinct users if looking at it per month.  That is because there were in fact 4 distinct users taking tests each month.

    5.png

     

  • if a name already appeared in a previous month, it shouldn't be counted for the following months. 

     

    the name should only appear on the month that they first appeared in. 

     

    unfortunately, it is not happening right now using the count distinct function in beastmode.

     

    is there a way maybe in magic etl?

  • Yes, you would want to calculate the earliest start date for each user.  Then you could create the graph using the earliest date as the x-axis and the distinct count of resource name as the y-axis.

  • unfortunately, right now, the count distinct is showing duplicates and counting them as distinct.

  • Is there a reason that you don't use the five9 account number?  Why are we counting unique names?

  • vecause there can be multiple test taker per same account.

     

    meaning there can be same account number but different names

     

    for example

     

    1/1/2019 12345 WorldbreakerHulk

    1/23/2019 12345 Superman

    1/29/2019 12345 Superman

    1/30/2019 12345 Superman

     

    the total for january would be 2 

    they are all with the same account number.

     

    if we use the account number the count will just be 1 since the other 3 will be duplicates. 

  • If you use the group by tile to find the earliest test start date per `test taker: Resource Name` and then join that back to the original data, you can build your graph.6.png

     

  • This looks like it did it!!! or close to it.

     

    what I am seeing now is the totals on some of the months are off by one or two..

     

    and deep diving, the missing names are unique names... 

  • Are there any null values for the date field?  What are the names that are missing?  It’s also possible that if your trim() or concat() functions are erring out then it won’t count either. 

  • there aren't.

     

    I tried removing the trim function but nothing changed.