1.7K members online now
1.7K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply
Highlighted

COUNT_DISTINCT doesn't work with a date dimension

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I'm using a google sheet full of call tracking data and I want to count the number of calls from unique numbers, so ignoring repeat calls. However, if the repeat call occurs on a different day to the initial call, GDS will count them all as unique numbers. Repeat calls on the same day are calculated correctly, but the date dimension seems to be causing a problem.

 

Is there any workaround for this?

1 Expert replyverified_user

COUNT_DISTINCT doesn't work with a date dimension

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆

Hi George,

 

Are you using COUNT or COUNTDISTINCT?

COUNT_DISTINCT doesn't work with a date dimension

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Sian, I'm using COUNT_DISTINCT

COUNT_DISTINCT doesn't work with a date dimension

Explorer ✭ ✭ ☆
# 4
Explorer ✭ ✭ ☆

I'm trying to replicate your issue with some dummy data, but I'm not hitting any problems.

 

My Count Distinct is working for calls on different days.

 

What are you trying to display the data in? A table?

COUNT_DISTINCT doesn't work with a date dimension

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi Sian,

 

If you use this as test data:

 

Phone Number Date
1111 01/01/2017
2222 01/01/2017
3333 01/01/2017
4444 01/01/2017
1111 02/01/2017
2222 02/01/2017
3333 02/01/2017
4444 02/01/2017

 

Then create a metric which is COUNT_DISTINCT(Phone Number), then create a scorecard using the metric with the date as a dimension, you'll see that the result is 8 rather than 4 when you set the date range from 01/01/2017-02/01/2017.

COUNT_DISTINCT doesn't work with a date dimension

Explorer ✭ ✭ ☆
# 6
Explorer ✭ ✭ ☆

I've done exactly as you described and I'm getting the correct answer of 4.

 

I've tried changing the field type's and aggregations and it doesn't make any difference. I've added filters and tried different date ranges, but nothing seems to cause the issue you're experiencing.

 

Try disconnecting and reconnecting your data source.

Re: COUNT_DISTINCT doesn't work with a date dimension

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hi Sian, I've attached some images to show you how I've setup the test data, because I'm still not getting the correct figure!

 

Capture.JPGcount.JPGcountdistinct.JPGresult.JPG

Re: COUNT_DISTINCT doesn't work with a date dimension

Explorer ✭ ✭ ☆
# 8
Explorer ✭ ✭ ☆

Ok, the only difference between our set ups is the selected date range.

When I have All Date Range selected, the Distinct Count displays 4, but when it is changed to 01/01/17 - 02/01/17, it switches to 8.

 

In fact as soon as you put any date modifier on, other than All Date Range or No Date Range, the distinct count is wrong. Definitely a bug in the system!

 

I'm struggling to think of a work around at the moment. Adding something to the initial Data Source may be the only option.

COUNT_DISTINCT doesn't work with a date dimension

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

not just me then!

 

hopefully Google can get it fixed pretty quickly!

COUNT_DISTINCT doesn't work with a date dimension

Follower ✭ ☆ ☆
# 10
Follower ✭ ☆ ☆

I'm testing again and it seems to be working correct COUNT_DISTINCT function with a filter control (between start date and end date) 

: )