change date format

I created a derived table that count the leads that we got  in each day:

34df64fc-322c-4d25-a80a-bdcab3aebe18.png

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:

49dd620e-bc4f-466e-aa8c-10586e139f8c.png

Do you have any suggestions? other solutions?

0 2 489
2 REPLIES 2

Dawid
Participant V

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

leobardor
Participant V

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:

6157214a-0e51-4548-a22d-a0a4b1806d83.png

Hope this helps!

Regards,

Leo

Top Labels in this Space
Top Solution Authors