I created a derived table that count the leads that we got in each day:
Instead of that, I want to count the number of the leads we got each MONTH. For that I need to cast the dates to a different format (MM-YYYY).
I tried to use the FORMAT function, but the query failed:
Do you have any suggestions? other solutions?
Use dimension_group in your LookML, feed it your time field and this way you will get a group of timeframes that will then take care of returning a date value that corresponds to first day of the month
https://docs.looker.com/reference/field-params/dimension_group
Hi Meitrar.
We can also create this combination by extracrting year and month of the date field:
SELECT EXTRACT(YEAR FROM organization.created_at), EXTRACT(MONTH FROM organization.created_at),count(1) FROM
bites-new-api-ga4-report.reports.bites_organizations AS organization
WHERE lead_name='hubspot'
GROUP BY created_at
;
For instance:
Hope this helps!
Regards,
Leo