Filter on "Month" is actually filter on "Date"

[Rui_Zhang1]

Anyone else got confused by this?

I used “type: date_month” in Lookml to generate the month. But I just noticed it’ll only re-format the final result in the “select” statement to “Month”. It won’t apply to filters.

My expectation is that “Month” is always formatted to the first date of the month, and when I do Month is in the past 365 days, filters would be the first date of the month is in the past 365 days so it would ignore May 2019.

I can see the benefit of doing it either way though but intuitively I was expecting the other way maybe because of the other tools we use.

To get around this, I can’t use the default “type: date_month” Lookml. Instead, I need to use SQL to get the First Date of the Month.

[Cyril_MTL_Analy]

If i’m not mistaken, whatever the timeframe you select as a filter, the where clause will essentially be the same.

[Rui_Zhang1]

Yup, I mean in this example. My expectation is that the “Start of the transaction month” is in the past 365 days but the filter is doing “Transaction Date” is in the past 365 days.
E.g. for 2019-05-16, transaction month is 2019-05 so the start date is 2019-05-01. It’s outside of “past 365 days”. So I don’t expect to see 2019-05 in the result.

[lizzy1]

If you wanted to get data for the most recent closed 12 months + month to date you can use the matched advanced filter and write “after 12 months ago”. This will give you the most recent complete 12 months + all the days in the current month.

For example, if you wrote that today (5/16/20) you would get data from 5/1/19 to 5/16/20.

If you want to ignore May of 2019 then you would write “after 11 months ago”. For example, if you wrote that today you would get data from 6/1/19 to 5/16/20.

[Rui_Zhang1]

Hi Lizzy,
Thanks for your reply. That works, but basically it’s still filtering on “Date”, not the “Month Start Date”.
I have a dashboard with a date filter, and I was hoping to use that to filter to control multiple tiles that are based on a combination of date, the start of the month, the start of the week, etc. So I feel like I really need to create a dimension that’s “Start of the Month” instead of “type: date_month” to make it work.

[Cyril_MTL_Analy]

Can you expand on your use case with maybe a screenshot of the dashboard?

I think we could achieve what you want with a difference approach