Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Templated filter on dimension group

  • 18 January 2019
  • 4 replies
  • 931 views
T

Hi,



I have templated filters on derived tables on date. Unfortunately, it looks like I can’t apply a templated filter to a dimension group so I need separate filters for each timeframe. This causes two problems:





  1. It’s an unnecessary amount of code, but it works. See below.


  2. I’d like to have default filter values in case people forget to filter on date, which causes a massive query to run on all of our sales history. I could do something like this, but it won’t work because if someone for example filters on report_week instead of report_date, the report_date templated filter will fall back on only pulling yesterday.






    and {% if report_date._is_filtered %}  {% condition report_date %} rpt_dt {% endcondition %} {% else %} rpt_dt = date_add('day',-1,current_date) {% endif %}




Would it be possible to allow templated filters on a dimension group? Or any other ideas that would work?



Thanks,


Tim





    and {% condition report_date %} rpt_dt {% endcondition %}


    and {% condition report_time %} rpt_dt {% endcondition %}


    and {% condition report_week %} rpt_dt {% endcondition %}


    and {% condition report_month %} rpt_dt {% endcondition %}


    and {% condition report_month_num %} rpt_dt {% endcondition %}


    and {% condition report_month_name%} rpt_dt {% endcondition %}


    and {% condition report_quarter %} rpt_dt {% endcondition %}


    and {% condition report_quarter_of_year %} rpt_dt {% endcondition %}


    and {% condition report_year %} rpt_dt {% endcondition %}


    and {% condition report_day_of_week %} rpt_dt {% endcondition %}


    and {% condition report_day_of_week_index %} rpt_dt {% endcondition %}


    and {% condition report_day_of_month %} rpt_dt {% endcondition %}


    and {% condition report_day_of_year %} rpt_dt {% endcondition %}


    and {% condition report_fiscal_quarter_of_year %} rpt_dt {% endcondition %}


    and {% condition report_fiscal_month_num %} rpt_dt {% endcondition %}


4 replies

T

Hey Izzy,



Thanks for the follow-up.



A filter field won’t work because if a user then filters on the regular date dimension rather than the filter field, the derived table won’t have any filter on date.



Any suggestion for how to solve #2? I’d like to force users to always have a date filter on our derived tables so they never run for all of history. I don’t think always_filter would work because again we’d have the same problem where one of the dimension group timeframes would have to be specified.



Would it be possible to make a change so dimension groups can be used in templated filters (ie report rather than report_date, report_time, etc.).



Thanks,


Tim

izzy
Userlevel 7
Badge +1

That seems like the way to do it to me, bulky as it is.



A colleague of mine, @shiggins, suggested using a filter instead of a dimension_group in the derived table to keep the logic smaller, yet maintain flexibility on the filtering:



  filter: date_filter {


    type: date


    sql: {% condition date_filter %} ${users.created_date} {% endcondition %} ;;


  }
izzy
Userlevel 7
Badge +1

I was going to suggest an always_filter, but I think you’re right about that. You could do a sql_always_where with a big if/or statement that covers all the dimension_group options for view_name.field_name._is_filtered…



A potential way to catch the edge case you mentioned of





could be to add a liquid statement where you say if date dimension is filtered on and date filter isn’t, capture the filter value from the date dimension, and apply it to the SQL.



But then you’re getting kind of rabbit-holey, especially if we have to do this for every dimension group timeframe-- that’d be a lot of lines of liquid.



You could also just add can_filter: no to the date dimension, which might be the easiest solution!



I’ll keep thinking about this!

Ashita

Hi,



I have two custom measures which I have created on template filter(Date).


Eg. 1) Measure to calculate on Orders created date


2) Measure to calculate on Orders received date



Now I have to show MoM Orders Created/ Orders Received on this template filter. In short I want template filter to be as a dimension, so that I can pivot it.


Please help.

Reply


Terms of Service