IssueTracker

Can't COUNT_DISTINCT() over a specific date range!!! This appears to be a common problem! Please fix ASAP!

Instead of counting unique values in a specified column COUNT_DISCTINCT counts rows in a database (on a view, not a table, to be more specific)... It works as expected with spreadsheets.

Having the same issue with count distinct when selecting a specific filter

From my side, I'm using Postgres Database (All good in Postgres doing the same grouping-unique, so no issues in DB or in Data).
If no values are selected in Filter Control widget the COUNT_DISTINCT is working as expected as it's getting Unique values for the entire dataset, but when I put a value in Filter Control, COUNT_DISTINCT is generating a wrong value.

I've run into the same issue. I'm connected to Postgres. When I apply a date filter to any metrics that use COUNT DISTINCT, the results wind up being COUNTs.

I can't believe that Google has left this bug unresolved for months, this is a joke. Any time we apply date filtering, and try to utilise count distinct aggregation, when we apply further filtering through other dimensions, the metrics become extremely overstated.

Please sort this out Google, we will have to move to a different tool as this is fundamental functionality...

My read on this is that Google doesn't care about Postgres/Redshift support for GDS, and that anyone not on BigQuery should choose a different BI tool. It's really unfortunate because GDS seemed to have a lot of potential.

We're using Big Query and have similar issue, don't know how long we have
to wait to get this resolved.

On Tue, Feb 4, 2020 at 1:02 PM <buganizer-system@google.com> wrote:

Yeah, I don't think this is connector specific. It more so seems like a bug in how Data Studio handles count distinct aggregation when applying a date range and in conjunction with filtering with other dimensions. I created another thread for this here: https://issuetracker.google.com/issues/148813902

Feel free to jump in on it and comment as well, this bug is affecting us massively here.

On our side it's probably working with a view vs actual table. Funny enough same data with the same filters displays in expected number of rows (so it can distinguish between unique entries properly (same string)).

Interesting. I am using Postgres and I gave your idea a shot, but a view and a table result in the same bug. However, I also tested to load the exact same data source onto Google Sheets and then load it onto Data Studio and the problem was resolved. So I was wrong^^ it IS connector specific. I guess the guy above^ is probably right. I'm pretty sure Google is aware of this but doubt they will ever fix it.

Best case if any one stumbles upon this 1) Either use a proxy like GSheets to load your data on there first if you are experiencing this bug with MySQL or Postgres or whatever other engine, or 2) Switch to a different BI tool!

Using Redshift with the Postgresql connector. When I render the individual values as a table, everything is correct, but when I render a COUNT DISTINCT in a score card I get incorrect numbers similar to what has been described above. We've really liked Data Studio so far as a BI Tool but it's a serious bullet if it can't do such a basic counting function correctly. The worst thing to present in a business report is data that is obviously wrong.

Same problem here. Count_distinct(column) fails when multiple values of a filter is applied.
Very Critical for us.

It's incredible given how debilitating this is, how long this bug has existed 😢

Same problem on Postgres, please get this fixed, it GROUP BY on the date range dimension field so it's unable to COUNT DISTINCT properly

I've ran into this issue before using a Postgres connector but I've ran into it again now using the file upload (.csv) connector too. Interestingly, the Data Studio Explorer shows the correct values (using same config)!

I'm having the same issue, reading from a CSV and unable to count_distinct() a text field. Somehow this is fixed if I blend the data

Same here. Postgres connector. count_distinct() returns the value as if it were grouped by day and then summed, rather than a true distinct count for the duration of the date range selected

Same issue here...csv file, when a data range is selected, them returns incorrect value

same here. Mysql connector. Data range filter + count distinct >> wrong value

Same issue. We solved by combining the data with itself, but it's a very dirty solution. Wait for a real fix !!
I also try using directly a calculated field with COUNT_DISTINCT() but it gave the same wrong result.

This has nothing to do with connetors or anything.

The problem is probably that COUNT_DISTINCT get's executed before the filter and afterwards the filtered values get aggregated together (mostly summed). It's a real Deal breaker ...

Same issue here, from postgres connector. I would really appreciate if some time was allocated to fix this issue.

Same here. Postgres connector. count_distinct() returns the value as if it were grouped by day and then summed, rather than a true distinct count for the duration of the date range selected

It fails when dimension is a date and metric is CTD. I use mysql connector and experiencing same issue.

+1 It fails when dimension is a date and metric is CTD. I use mysql connector and experiencing same issue.

+1 same problem here wtih PostgreSQL connector.

+ 1 same problem here with CTD and PostgreSQL

We are also facing this issue, especially when doing a roll-up to year-week. Funny thing is, in some cases it works and in other cases not.
I even get different results on two different data sources while both of them use the same custom query.
 I can not put my finger on where the difference is.

EDIT 1:
I was able to get arround the issue by adding
CAST(DATE_FORMAT(date, '%Y%u') AS CHAR) AS year_week
to the query. Using this as a dimension works fine.

EDIT 2:
After having added filter controls to the page, the described workarround ceased to work on that page. On another page that uses the same data source, it still works however.
The next strange thing is, that with my described workarround I still get correct numbers in a pivot table visualisation but not with stacked column chart visualisation. It becomes even more weird as I already successfully applied my workarround to another stacked column visualisation.

I am baffled and this issue is indeed a huge annoyance.

It seems to be the number of filters applied which causes a problem.

I seem to be having this issue with a csv upload. Data studio sums up the field as opposed to counting unique accounts. This is only an issue when multiple drop downs are selected from the filter. It works as it should at total level or with one filter selected.

Same issue, distinct count is not working on applying the date range filter. However, it populates correct result without any filter.

Same here.

+1

+1

+.1 I have noticed count distinct displaying incorrect numbers after a filter has been selected, and decided it's best to stop using it until a fix has been implemented properly.

Hi.

I was able to easily reproduce the issue - Google - It's time to fix this - Its becoming a real show-stopper for your platform.

How to reproduce:
1. Create a new Postgres data source.
2. Use the following custom query:
select '1-jan-2020'::date as sold_date, 'text1' as name
union all
select '2-jan-2020'::date as sold_date, 'text1' as name

3. Build the data source, and create a report. put the sold_date as the date dimension, and count and count distinct the names.

You can see the issue happening in the screenshot. Count = 2 while Count Distinct should be 1 - but shows 2.

Why this is happening:
-----------------------------------
The reason for this bug is the underline query, sent by the Google Data Connector to Postgres:

SELECT name, COUNT(DISTINCT name) AS qt_53gfjojccc, COUNT(1.000000000000000) AS qt_mct0dojccc, sold_date
FROM (select '1-jan-2020'::date as sold_date, 'text1' as name
union all
select '2-jan-2020'::date as sold_date, 'text1' as name) AS t0 GROUP BY name, sold_date ORDER BY qt_mct0dojccc DESC

As you can see - the grouping includes the sold_date field - which will return separate distinct values for each day. Summarising them is a wrong distinct calculation.

BTW Google - why aren't you passing the date dimension as a WHERE filter - Your queries query all the data on the table - without the need - causing a bad performance and long running queries.
This is related, because the right way to do this distinct calculation is to use the date in the WHERE clause and not group by date.

Google - please fix this.
Lior Ash

I spend so much time for nothing because of this bug, unbeliveable!

What is the status on this?

I have the same issue.

+1

We're also impacted by this issue - using the Postgres connector, count distinct (on non-primary key fields) is incorrect on scorecards, tables, ... when applying a date filter.

I don't have anything new to add - post #38 above has perfectly explained the issue: distinct counts are generated per day and then summed, which is incorrect as the distinct counts need to be performed across the entire subset.

Belated Happy Birthday little bug.  One year old!

Congratz! Seems like you're going to have a long life Little bug:)

any news in this fix!?

Switched to Grafana, totally worth it.

It's been a year since this was reported originally... A fix would be great!

Or even an acknowledgement that somebody is listening.



*Chris McCauley*
Chief Technology Officer

e: chris.mccauley@qstream.com
a: WeWork, Level 3, 5 Harcourt Road, St Kevin's, Dublin 2, Ireland

*TRANSFORM LEARNING* |  Engage.  Reinforce.  Analyze.



On Thu, Nov 19, 2020 at 6:01 AM <buganizer-system@google.com> wrote:

Same issue here. Any progress?

Please, would be great to have an answer if this is going to be fixed or not, so then we can finally keep or move to other products... Thank you.

+1

On Fri, 20 Nov 2020 at 2:37 PM <buganizer-system@google.com> wrote:

--
[image: Ayenda]

*Andrés Sarrazola*
CEO | *Ayenda Hoteles*
Celular:  +573015945919
Web:  ayenda.com
email:  andres@ayenda.co
Dirección:  Calle 7D #43A 117 - Torre Almagrán - Piso 9
Medellín - Colombia
<https://www.facebook.com/ayendahoteles/>
<https://www.instagram.com/ayendahoteles/>

+1

Was enjoying GDS until I spent an evening trying to track down why our dashboard was completely wrong in counting users.
Sounds like best available solution is "Don't use GDS"?

I've discovered this incredible bug yesterday. Hope there will be a solution to fix this... (there is no problem when the source is a google sheet, but with PostgreSQL or CSV files)

+1
More than one year later this bug still persists...

Someone should change the Status from 'New' to 'Getting kind of old'

+1

Hi everyone,

Thanks for upvoting this issue and sharing all the helpful details. We’ve investigated the issue and are actively working on a solution. As a few of you have noted, there's an issue that's connector-specific (it applies to the Cloud SQL for MySQL, MySQL, and PostgreSQL connectors). When charts/visualizations are filtered, or grouped by calculated dimensions, and perform specific aggregations such as Count Distinct, the generated query can yield incorrect results. While we wish this were a quick fix, it's not. We're actively working on an overhaul of the query generator to address this issue. Timing may change as work progresses, but we estimate we may be able to roll out an improvement this summer. We'll post any updates here as we get closer to rollout or the timeframe changes.

That's great news! Good luck with that, we'll be cheering for you.

:+1

I have the same issue  

I have the same issue. In my case count distinct shows the sum of count distinct but over the filtered periods (months in my case).

+1

Need this issue to be resolved ASAP.

+1.

The same for me. PG queries. CTD works as an ordinary "count" and it doesn't work for all queries where I use datetime.

I have to use DATE(created_at)/to_char(created_at,'YYYYMMDD') to make it work

We've given up waiting and moved to Amazon.

+1

Hi everyone,

We’ve now rolled out an improvement to these connectors (PostgreSQL as well as MySQL and Cloud SQL for MySQL) to address this issue. The improved connector generates SQL queries that more consistently yield accurate Count Distinct results. It does this by performing all filtering and grouping in the SQL query when possible, or by querying unaggregated data when necessary. (Some calculations don’t have a direct translation in a particular SQL dialect, such as PostgreSQL’s.) We’re eager to hear your feedback about whether this improvement resolves your issues.

I am having this issue again

I too am still having this same issue

I am having this issue using BigQuery as a data source

this is not fixed

For me GDS was not showing a correct count_distinct when displaying data in a chart. Since my data source are quite old I added them once more and created new charts and now it works.

I am currently using the Google Cloud Platform using File Upload data, and the Count Distinct function doesn't seem to work with or without date ranges.  It was interesting to see that because I had 18 days of data, it seemed to multiply the numbers by the number of days in the month...As I adjust the date to 1 day, 2 days, the count distinct seemed to multiply each time to those date ranges. Is there any further progress on this bug?  We are purely attempting to count the unique values for a field for a specific date range.

Change your methodology to connect it to a Google sheet

Dump your raw data in Google sheet every time you need to update

Fixed my count issue. Had chronic issues with file upload

On Sun, Oct 24, 2021 at 2:31 PM <buganizer-system@google.com> wrote:

--


James Alexander HeinenAssistant Product Manager - Global Portfolio/Master
Data
The Scotts Miracle-Gro CompanyC: 937-209-8879scottsmiraclegro.com

still not working with CSV import, just bumping this because we are April 2023

Going to have to rebuild using Google sheets or an SQl Connector

Still not working with CSV import when using date ranges. At report level without date filter seems to count correct. May 2023

This issue is still not fixed

It appears the team just never cared to look into a fix for the CSV connector. Still an issue years later.

I'm having the same issue, but I'm connected to a bigQuery table and the count distinct seems to be broken only with a specific id field, and it's not like "kinda broken" its counting 4 where its supposed to be 500+

having the same issue i made the manual and the difference between distinct count its now like 1000 its to much to considere this a slight percent of counting difference or something, why last year was working correctly and this year is having this issue that even if F = F on excel its different according to data, i had to make some arrange to make it locket studio do the right and its really bothersome

I am also experiencing this issue-- when the same data is placed in a table the table creates the correct number of unique rows, but, when using the scorecard the "count" and "count_distinct" options produce a total record count that is identical.

A problem has started occurring in the past week in which COUNT_DISTINCT returns incorrect values when it is applied to an expression that modifies a field - the unique values of the underlying field are returned and the expression is ignored eg COUNT_DISTINCT(DATETIME_TRUNC(datevalue, WEEK)) returns the number of values of datevalue not the number of weeks (which would usually be less)

I'm trying to use count distinct with date range and the results are wrong. I'm using with a CSV connector. That's sad that they didn't correct yet.

Found a solution for this for data source via csv connector (or actually GCP cloud storage to csv files)

We have a column named "Delivery Date" in a csv. Values are on the following format "YYYY-MM-DD", e.g "2023-12-14".

By creating a new calculated field (we just gave it the same name, and hid the other one) it it started to work for us.

PARSE_DATE(“%Y-%m-%d”, CAST(Delivery Date as TEXT))


Also, as mentioned by many others here, it seems to be a problem with connecting to csv files. We tried importing the full same csv (62,000 rows) to a Google Sheet, and then using it as a data source. Count distinct works fine with that data source.

See attached screenshot.