Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Selecting Only Unique Records for a Total Count

  • 6 October 2017
  • 4 replies
  • 3188 views
C

I have a use case where I have duplicate records but difference dates and I want to get a count of Unique Records by only selecting the first recorded Record


For example,


Data Set:

UID-Date-Value

A 6/12 Registered

A 6/12 Registered

A 6/13 Registered

B 6/14 Registered


Want to Return:

UID-Date-Value

A 6/12 Registered

B 6/14 Registered

Count = 2


How can I do this in Looker?

4 replies

brecht
Rank
Userlevel 4
Badge

Hi @chadvanholland, sounds like if you can achieve this by creating a measure of type: string with the sql as MIN(date) and another of type: count_distinct for UID. If then add the dimension UID and the two measures, you should achieve a similar result. Hope this helps!

C

Thanks!

I can’t seem to get the mindate to work: min(trunc_days(${events.created_month}))


Min doesn’t seem to work with dates.

jeffrey.martinez
Rank
Userlevel 3

Hi @chadvanholland,


You’re totally right, within a Looker table calculation the MIN function does not accept dates. In this case you would need to use extract_days to extract a number, instead of trunc_days which extracts a date.


For your use case, to perform what you are looking for would be a little bit more involved, including writing a Persistent Derived Table. What Brecht was describing was making new measures within the LookML. The MIN function he was proposing should work in most SQL dialects. Do you have access to edit the model LookML?

C

I don’t think I have access to edit the model but I will check with the Vendor I am using. Thanks for your help!

Reply


Terms of Service