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
-
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.
**If this answer solved your problem be sure to like it and accept it as a solution!
1 -
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
we actually need the date as we use this as the basis for the graph when we separate them by month.
0 -
I am not sorting it with anything.
0 -
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`))
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'1 -
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.
1 -
-
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.
0 -
-
-
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.
0 -
You are sorting by MIN Test Started which will cause dups to show up.
**If this answer solved your problem be sure to like it and accept it as a solution!
0 -
I'm not sure why you are getting a different result
0 -
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 ?
0 -
it needs to be in a bar graph.. I just showed the table to show the duplicates being counted as distinct.
0 -
also, when I did the MIN in the corrected date in a table, It showed far less data now... a lot less...
0 -
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.
0 -
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?
0 -
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.
1 -
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?
0 -
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.
0 -
unfortunately, right now, the count distinct is showing duplicates and counting them as distinct.
0 -
Is there a reason that you don't use the five9 account number? Why are we counting unique names?
0 -
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.
0 -
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.
0 -
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...
0 -
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.
0 -
there aren't.
I tried removing the trim function but nothing changed.
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 637 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 761 Beast Mode
- 65 App Studio
- 42 Variables
- 704 Automate
- 182 Apps
- 458 APIs & Domo Developer
- 53 Workflows
- 11 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 132 Manage
- 129 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive