distinct count not working

WorldWarHulk

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?

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.

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.

WorldWarHulk

here you go

WorldWarHulk

here you go

 

 

 

 

 

WorldWarHulk

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

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.