Hello helpful bunch
Another question about comparing different dates, this time looking to compare Month to Date to the previous month. We have a “Month to Yesterday” dimension, but I’m struggling to pull the same number of days for the previous month.
I was wondering whether there was a filter I could use which looks at the number of days gone by this month and then pulls in the same number of days in the previous month?
Any suggestions welcome!
If you need something quick, you can include the
day_of_monthinto your timeframes on the date dimension group, then use it as a filter. The downside of this is having to manually specify a number. But, you could do
in the past 2 monthson the
less than 17(or whatever today’s happens to be) on the
Otherwise, here’s something I just tried and seems to work:
day_of_monthto your intended time dimension group
Go to your explore. Put a filter on month for the past 2 months. Click your new filter-only field to exclude results that are before day.
Hope this helps!
PS: The motivation for the getdate() wrapper is to preserve whatever timezone logic you may have set up in Looker. But you can just do an extract day from getdate() style comparison if you’re not worried about that.
We do that quite a lot for clients and here it is in essence:
Create a yesno dimension to compute the only display data where the date is between the first day of a month and the current day of the month
In your Explore:
Here is an example that shows that the data stops after the 18 of the month (which the days of the month when I ran the report):
*_day_of_monthtimeframe in your base dimension_group
If you happen to be unbelieavably lucky to have near real-time data, I use a view called current time that looks like this:
It can be simpler but that depends on date functions in your dialect.
Then I join it to let’s say
ordersview and create
before second of dayor
before second of quarter