Get Started Transforming Your Data in Snowflake

How to capture the count of records for a date between two dates in Looker?

  • Datameer, Inc.
  • January 22, 2018

Assuming that the Date Dimension is already created, this can be achieved by creating count measures in the LookML of the same view where Date dimension exists:

  1. Count Distinct Example: If Distinct Count needs to be done on a non-primary key field, one can create the following measure in LookML.

    measure: count_distinct_example {
    type: count_distinct
    sql: ${TABLE}.field_name ;;
    }
  2. Count on a Non-Primary Key field: If a normal count needs to be done on a non-primary key field, one can create the following measure in LookML.

    measure: count_example {
    type: number
    sql: count(${TABLE}.field_name) ;;
    }

  3. Count on a Primary Key Field: If count needs to be done on a primary key field, one can create the following measure in LookML:
    (Assuming dimension “field_id” is already created in LookML view and is defined as a primary key)

    measure: count_example {
    type: count
    drill_fields: [field_id]
    }
  4. Alternatively, count_distinct on any dimension can also be created on the fly in Explore itself. Next to every dimension name in the explore, an option is provided for creating a custom measure. Please note:

    1. For dimension type string – the custom_measure types available are COUNT and LIST.
    2. For dimension type numeric – the custom_measure types available are COUNT, SUM, MAX, MIN, etc.

    Here the custom_measure type COUNT will always resolve to COUNT_DISTINCT in SQL.

Once the Count measure has been created, pull the date dimension and the count measure in the explore grid. Hence the count would be grouped by each date record.


Up Next:

Read How to stop TinyInt converting to Boolean in Looker?

More Resources We Think You Might Like

How to create a funnel chart visualization in Looker?

How to comment on an entire block of code in th...

In Looker, one can follow the below instructions to comment a block of code in LookML editor:  Se...

  • Datameer, Inc.
  • January 22, 2018
How to create a funnel chart visualization in Looker?

How to add image from my local drive (or locati...

We can use the HTML parameter in the LookML of the Dimension / Measure to have Looker render the ...

  • Looker FAQs
  • January 21, 2018
How to create a funnel chart visualization in Looker?

How to change the order of stacks in a stacked ...

A stacked area chart is an extension of a basic area chart. It is used to group values of multipl...

  • Datameer, Inc.
  • January 25, 2018