Custom filter on the look

[slamba]

Hello,

I am trying to create a customer MTD and QTD measure using the custom date filter on the Look.

I was reading a few forums and I found this formula:

 (extract_days(${d_tim_day_lu.Date}) > extract_days(now())
     AND extract_months(${d_tim_day_lu.Date}) = extract_months(add_months(-1, now())))
  OR
  (extract_days(${d_tim_day_lu.Date}) <= extract_days(now())
     AND extract_months(${d_tim_day_lu.Date}) = extract_months(now()))

but this does not solve my issue. 

I am trying to calculate the MTD data. For example if I pull the data on December 6th, it should give me the data from December 1st to December 6th (MTD).

does someone know of a work around to get this??

[Dawid]

(extract_days(${d_tim_day_lu.Date}) <= extract_days(now()) AND extract_months(${d_tim_day_lu.Date}) = extract_months(now()))

This is a code that would do exactly what you’re asking, what exactly isn’t working?

[slamba]

@Dawid this works partially, the data also includes previous years. so for example I am getting data from December 1st to December 6th but for 2020, 2019, 2018 , 2017 …. I just need the data for the current year

[Dawid]

Then you need to add one extra line to only show current year using extract_years()

[slamba]

(extract_days(${d_tim_day_lu.day_key}) <= extract_days(now()) AND extract_months(${d_tim_day_lu.day_key}) = extract_months(now()) AND extract_years(${d_tim_day_lu.day_key}) = extract_years(now()))

@Dawid I added another line to only show the current year using the formula you gave me. the problem which I just noticed is that the data does not include the entire month. The data is starting from December 2nd but I also want this formula to include December 1st. 

[Dawid]

I don’t think this is a filter issue then

extract_days(${d_tim_day_lu.day_key}) <= extract_days(now())

The above says that as long as the month and year is the same, then anything prior to today and including today will be included. Are you sure there is data to show for Dec 1? 

Do you get results in SQL in your native DB when you apply this logic?

[slamba]

@Dawid Yes there is data for December 01, 2020. 

Below is the screenshot of how my filters look like. Also the way my data is set up in DB it is mandatory to have one time filter (either Last Week - Yes/No, Yesterday - Yes/No or the calendar date filter) due to the amount of data.

[Dawid]

What if you remove your other filters one by one? Does the data show up then? 

[slamba]

This formula works perfectly now ! I added the year filter at the top. The only other issue is that we follow the fiscal calendar so technically the month of December started on November 29th. Is there a way to tweak this formula to follow the fiscal calendar ? sorry I know I have been throwing questions at you. 

but THANK YOU so Much for this @Dawid  !!!

[Dawid]

I think you could but it depends. Does your fiscal calendar starts on 29th of each month or are there rules to it like, for example “last Sunday of the month”?

If so  then I would build a calendar table in your source, expose it as a view in Looker, and then use it.

What I usually do is I calculate a day, week, and month offset from today. You could create fiscal_month_offset column that would be -1 meaning previous fiscal month and 0 current. The good thing is that in SQL you can use all the advantages of your dialect to replicate the business logic you need. Then in Looker, you only use the ready fields.

This is a peek from our Aggregation explore:

I can use Month Offset = 4 to look at the same month last quarter or let’s say Week offset if I want to look at a specific week in the past . The idea is that each activity date is joined with calendar and that enriches the date with plentiful of useful information and it allows me to not use a lot of date manipulation within Looker. Here’s what it looks like in SQL: