Filtering data using two dates in LookML

[Simant_Sah]

Hey guys!

I’m trying to get the count of leads between two dates. Below is the LookML I’m writing in order to filter it by dates:

measure: q3_leads_and_contacts {
type: number
sql: case
when extract(month from ${createddate_date})>=3 or extract(month from ${createddate_date})>=6
then ${count}
end
;;
}

However, it’s giving me an error saying “createddate should be in the group by clause”. What am I missing here?

[Cyril_MTL_Analy]

Hey @Simant_Sah,

The issue here is that you’re using a dimension in a measure that is not aggregated (type: number).

What you could do is create a yesno dimension and filtered measure:

dimension: is_between_dates {
  type: yesno
  sql:  ${createddate_month_num} >= 3 and ${createddate_month_num} <= 6  ;;
}

measure: filtered_count {
  type: count
  filter: [is_between_dates: "Yes" ]
}

By the way, as you can see I took the liberty of changing your SQL (adding a new timeframe “month_num” to your createddate dimension_group and replacing the or by an and which makes more sense)