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?
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)