Date filter up to current point in time

Dawid
Participant V

One of the first things I do when I use new BI tool is to come up with sets of dashboards that compare the following:

Today vs yeterday up to the same hour+minute
Today vs same day of previous week
This week vs previous week

etc.

So far I can’t find a good way of specifying that kind of filter. Even taking a day of the week index needs workarounds and using multiple functions. I tried using Custom Filter but also can’t find anything that would achieve what I need. There’s no simple day, month, hour function that would just extract the integer from current date.

trunc_hours doesn’t seem to work - is it, in the background, still a different hour if it’s a different day?

0 14 11.3K
14 REPLIES 14

You’re right that there’s no super-simple inbuilt way to do this kind of period over period analysis, but there are a few resources that break it down.

This one: https://help.looker.com/hc/en-us/articles/360001189687-How-to-do-Period-over-Period-Analysis is a bit more simple, and this:

[Analytic Block] Flexible Period-over-Period Analysis Has some more complex patterns that let you do dynamic period analysis. Have you checked those out already?

Hi Dawid,

If this is a common need, you could create a dimension whose sql is CURRENT_DATE (or whatever the equivalent is for your dialect) and apply various timeframes (like day_of_week, etc) to it.

You could even put this dimension in its own field-only view for convenient reuse across explores.

Then from the explore interface, users can for example dimension by week, and apply a custom filter that compares the day_of_week for the date field of interest to the day_of_week for the “current date” field.

Dawid
Participant V

This is an interesting idea. I created the following view:

view: current_time {
  derived_table: {
    sql: SELECT CURRENT_TIMESTAMP::TIMESTAMP AS "current" ;;
  }
  
  dimension_group: current_time {
    type: time
    timeframes: [time, hour, hour_of_day, day_of_week, day_of_year, date, week, month, month_num, month_name, quarter, day_of_month, year, raw, week_of_year]
    sql: ${TABLE}.current ;;
    
  }
}

do I now have to specify joins to all other explores and by joining on all the date dimensions I have?

Actually I don’t even know how to join it? It’s not really a typical one_to_one or anything.

I tried to use extends and the name of the new explore but it actually does nothing to the explore that’s extending it. No new fields show up, but the current_time explore works perfectly fine on its own

Hi! Sorry if my suggestion had an air of mystery to it 😉

The way I would do this is:

view: current_time {
  # No derived table or sql table name
  dimension_group: current_time {
    type: time
    timeframes: [time, hour, hour_of_day, day_of_week, day_of_year, date, week, month, month_num, month_name, quarter, day_of_month, year, raw, week_of_year]
    sql: CURRENT_TIMESTAMP::TIMESTAMP ;;
  }
}
explore: anything {
  join: current_time{
    relationship: one_to_one
    sql: ;; #Note `sql` not `sql_on`. Basically not changing the resulting join SQL
  }
  join: other_things_as_normal {...}
}

Dawid
Participant V

Is there any reason why I can’t use time of day in Custom Filter?

If this dimension can’t be used in filter and is a String… then how can I use it if there’s no minute_of_hour to help me calculate a numeric value of current time.

For example:

12:03 would be 723 aka minute_of_day

Looking into why that can’t be used in a custom filter, I also experienced the same thing.

You could use the full time dimension and extract_hours()/extract_minutes() in a custom filter to reconstruct a time_of_day, though.

bens1
Participant V

Would you advise using a derived table structure like this to house things like Rolling Date durations likely to be used in multiple explores, by relating them (and joining Base Explores) to that Current Date dimension? I could see that design being really nice to maintain by centralizing like that.

Dawid
Participant V

I have done that, just too bad I have to calculate it in any look I want to use

I don’t think I 100% follow… I would have to see an example to be sure what exactly you mean.

bens1
Participant V

Let me try to rephrase my question. I have several fact tables that will require slicing and dicing in the same ways, with the same date sensitivity (rolling 2 weeks, rolling quarter, etc.). Rather than building measures with specific filtered periods on an ongoing basis, I was hoping I could build in some date functions where I can easily reference them for those multiple fact tables, without having to repeat that date filtering code in multiple places. I hope my second attempt at explaining this was better than the first 🙂

Got it, makes sense now!

So, be warned, this is a bit involved and requires some practice… (we do have Professional Services offerings if necessary!)

That said… when modeling a multi-fact schema in Looker, the three broad patterns that exist are the “outer join on false” pattern, the “join paths” pattern, or more direct but verbose aggregate-before-joining pattern. I’ve published articles about the first two patterns on discourse. In all cases, though, I would recommend:

  1. Making an <explore>_date field-only view that is customized to the specific explore in which it will be used.
  2. Add a date/datetime dimension group to this view that coalesces between the various other views’ date fields of interest. Users will ultimately be pulling this coalesce into the outer/final where clause.
  3. If large datasets are involved, choose a specific date dimension which will be used for restricting scanning (usually the most granular dimension from the dimension group) and add it as an always_filter to the explore
  4. Use the sql parameter in the fact joins (or the pre-aggregated views if the pattern uses them) to apply the always_filtered date to the sort/partition column from the fact table with a liquid condition tag

Hope this all makes sense!

bens1
Participant V

Thanks so much fabio! I’d be lying if I said I’ve fully wrapped my head around what you’re suggesting, but I do like a challenge. I’ll make this one of my nerdy stretch goals. I’m also still in my Jumpstart sessions, so maybe I’ll bring it up and see whether my consultant thinks this is worthwhile at this point.

Dawid
Participant V

@fabio1 I’m in process of implementing it but wanted to change the view into a derived table. Unfortunately then the SQL changes to use WITH statement that takes its name from the view (I’m assuming).

So let’s say my view is called rightnow and it’s a derived table like this

SELECT
    CURRENT_TIMESTAMP AS 'time_now'

I know you said it’s not needed but I actually want to add more columns

Then in the join I reference it as this

join: time_comparisons {
    from: rightnow
    relationship: one_to_one
    sql:  ;;
  }

When I select fields they come up as: time_comparisons.time_now_day but the WITH statement is labelled as rightnow, which means I’m getting a relation time_comparisons does not exist

Do you have any idea what I’m doing wrong?

Hi Dawid,

Even though the WITH statement will be named rightnow, you would see that in the join statement in the final query, Looker would alias the join as time_comparisons, so that should work. Unfortunately, it would take looking at the whole view & explore to see what’s going on. If you don’t want to post the whole view & explore here, chat support should be able to quickly pinpoint the source of the disconnect.

Top Labels in this Space
Top Solution Authors