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?

data.jpg

Comments

Ashleigh

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

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.

WorldWarHulk

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

WorldWarHulk

I am not sorting it with anything.

Godiepi

@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`))

ST_Superman

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.

WorldWarHulk

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

data 2.jpg

ST_Superman

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.

data 4.jpg

data 3.jpg

Ashleigh

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

ST_Superman

I'm not sure why you are getting a different result

ST_Superman

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 ?

WorldWarHulk

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

WorldWarHulk

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

ST_Superman

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.

WorldWarHulk

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?

ST_Superman

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.

WorldWarHulk

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?

ST_Superman

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.

WorldWarHulk

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

ST_Superman

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

WorldWarHulk

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.

ST_Superman

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.

WorldWarHulk

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...

ST_Superman

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.

WorldWarHulk

there aren't.

I tried removing the trim function but nothing changed.

Quick Links

Find more posts tagged with

Other Categories

Product Ideas
Have a Domo product enhancement idea? Submit or upvote on ideas in the Ideas Exchange.
Ideas Exchange
Suggest & vote on new features you would like to see implemented in the Domo Product.
Data Connections
Ask questions about Connectors, Workbench, Cloud Amplifier and get best practices from Domo peers
Connectors
Connectors, Custom Connectors, Writeback
Workbench
Ask questions about Workbench, a secure, client-side solution for uploading your on-premise data to Domo.
Cloud Integrations
Ask questions about Cloud Integrations and Federated Data connection to your data warehouse or lake.
Data & ETL
Ask questions about Magic ETL, SQL DataFlows, DataFusion, Dataset Views and get best practices from Domo peers
Magic ETL
Ask Magic ETL questions and get answers from Domo peers
SQL DataFlows
Ask SQL DataFlow questions and get answers from Domo peers
Datasets
Ask DataFusion and Dataset Views questions and get answers from Domo peers
Visualize & Apps
Ask questions about Beast Mode, Cards, Charting, Dashboards, Stories, Variables and get best practices from Domo peers
Dashboards
Ask Cards, Dashboards, and Stories questions and get answers from Domo peers
App Studio
Ask questions about building apps in App Studio.
Pro-code Components
Ask questions about pro-code components and Domo Bricks and get answers from Domo peers.
Charting & Analyzer
Ask questions about charting and Analyzer and get answers from Domo peers.
Calculations & Variables (Beast Mode)
Ask questions about using calculated fields and Variables (Beast Modes) in Analyzer.
AI & Data science
Ask questions about DomoAI and get answers from Domo peers.
Domo AI & AI Chat
Ask questions about AI Chat and AI assistants.
Managing AI
Ask questions about managing AI with AI Playground, AI projects, AI models, and more.
Jupyter Workspaces
Ask questions about Jupyter Workspaces, Notebooks, and file share.
Automate
Ask questions about App Framework, Workflows, Domo Bricks, Domo Developer, API and get best practices from Domo peers
Workflows
Ask questions about Task Center, building automations with Domo Workflows, and executing JavaScript or Python code with Code Engine.
Alerts
Ask questions about managing alerts in Domo and get answers from Domo peers.
Distribute
Ask questions about Domo Everywhere, Scheduled Reports, Mobile and get best practices from Domo peers
Domo Everywhere
Ask questions about embedded analytics with Domo Everywhere.
Reporting
Ask questions about Scheduled Reports, Report Builder, and Slideshow Publications.
Manage
Ask questions about Governance Administration, Approvals, Teams, Alerts, and Buzz and get best practices from Domo peers
Governance & Security
Ask questions about People, Groups, Roles, Sandbox, Activity log, Buzz, Teams, Approvals and PDP and get best practices from Domo peers
Navigation & Productivity
Ask questions about navigation, Projects & Tasks, Goals, and Buzz chat.
APIs
Ask APIs and Developer.domo.com questions and get answers from Domo peers
Add-ins & Plugins
Ask questions about plugins, Microsoft add-ins, and other third-party software integrations.
Domo Community Gallery
Watch how our Customers are using Domo to solve their complex problems.
Product Releases
Domo support and product teams are here to live-answer questions about the most recent product releases. Please post questions in this Forum board for all users to benefit (rather than submitting a support ticket).
Domo University
Questions or discussions related to Domo University, trainings and certifications
Community Forums
Getting Started
Welcome to Domo's Community Forums! You'll find everything you need to get started in this category.
Community Announcements
Get the latest from Domo's Community Team.
Social Groups
Archive
Old or outdated content that could still be found helpful.