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

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?