field name couldn't be find (refinements issue?)

Looker Q&A Looker
,
1

I’m working an implementing this tutorial.

Context
Here is the architecture of my project:

I’m generating a fake dataset using derived_table function to avoid any confusion around the data set:

> derived_table: {> > sql:> SELECT> FORMAT_DATE("%Y%m%d", CAST(date AS DATE)) as date,> MOD(CAST(100*RAND() AS INT64), 100) as pv> FROM> UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2022-06-30')) AS date;;> }> ### The issue

In my page_view.view I want to add a new measure:

  measure: pageview_reference_advanced {
    label: "pageview sum ({% if parameters.select_reference_date._is_filtered %}Reference {% else %}Current {% endif %} {% parameter parameters.select_timeframe_advanced %})"
    type: sum
    sql: ${TABLE}.pv ;;
    filters: [current_vs_previous_period_hidden_advanced: "reference"]

this return me the following error:

I don’t understand why the field current_vs_previous_period_hidden_advanced couldn’t be found. It is defined in the PoP_dimensions.view (which is included in my page_view.view) like this:

view: +page_view {

#####  CURRENT/REFERENCE [Timeframe] VS PREVIOUS [Timeframe] with dynamic labels and default to today

  dimension: current_vs_previous_period_advanced {
    label: "Current vs Previous Period"
    hidden: yes
    description: "Use this dimension alongside \"Select Timeframe\" and \"Select Comparison Type\" Filters to compare a specific timeframe (month, quarter, year) and the corresponding one of the previous year"
    type: string
    sql:
      {% if parameters.select_timeframe_advanced._parameter_value == "ytd" %}
        CASE
          WHEN ${page_view.date_date} BETWEEN DATE_TRUNC(DATE_TRUNC(${parameters.selected_reference_date_default_today_advanced_raw}, YEAR), MONTH) AND DATE_TRUNC(${parameters.selected_reference_date_default_today_advanced_raw}, DAY)
            THEN ${selected_dynamic_timeframe_advanced}
          WHEN ${page_view.date_date} BETWEEN DATE_TRUNC(DATE_TRUNC(DATE_SUB(${parameters.selected_reference_date_default_today_advanced_raw}, INTERVAL 1 YEAR), YEAR), MONTH) AND DATE_TRUNC(DATE_SUB(${parameters.selected_reference_date_default_today_advanced_raw}, INTERVAL 1 YEAR), MONTH)
            THEN ${selected_dynamic_timeframe_advanced}
          ELSE NULL
        END
      {% else %}
        {% if parameters.select_comparison._parameter_value == "year" %}
          CASE
            WHEN DATE_TRUNC(${page_view.date_raw},  {% parameter parameters.select_timeframe_advanced %}) = DATE_TRUNC(${parameters.selected_reference_date_default_today_advanced_raw}, {% parameter parameters.select_timeframe_advanced %})
              THEN ${selected_dynamic_timeframe_advanced}
            WHEN DATE_TRUNC(${page_view.date_raw},  {% parameter parameters.select_timeframe_advanced %}) = DATE_TRUNC(DATE_SUB(${parameters.selected_reference_date_default_today_advanced_raw}, INTERVAL 1 YEAR), {% parameter parameters.select_timeframe_advanced %})
              THEN ${selected_dynamic_timeframe_advanced}
            ELSE NULL
          END
        {% elsif parameters.select_comparison._parameter_value == "period" %}
          CASE
            WHEN DATE_TRUNC(${page_view.date_raw},  {% parameter parameters.select_timeframe_advanced %}) = DATE_TRUNC(${parameters.selected_reference_date_default_today_advanced_raw}, {% parameter parameters.select_timeframe_advanced %})
              THEN ${selected_dynamic_timeframe_advanced}
            WHEN DATE_TRUNC(${page_view.date_raw},  {% parameter parameters.select_timeframe_advanced %}) = DATE_TRUNC(DATE_SUB(${parameters.selected_reference_date_default_today_advanced_raw}, INTERVAL 1 {% parameter parameters.select_timeframe_advanced %}), {% parameter parameters.select_timeframe_advanced %})
              THEN ${selected_dynamic_timeframe_advanced}
            ELSE NULL
          END
        {% endif %}
      {% endif %}
    ;;
  }
...
}

I think that there is something wrong in the way refinements is setup. any input is welcome.

1 Like
2

You’re using: current_vs_previous_period_hidden_advanced but in the view I only see current_vs_previous_period_advanced (without the word: hidden)

2 Likes
3

Hi Dawid, thanks for your reply. I did think about that but I was wondering if since hidden was some kind of parameters passed in the definition of the dimension it was not passed in the name of it in some way… (not sure what I’m saying make sense)

Anyway, I did update the name of the filter in my measure in my view but I have the same error:

page_view.view:

# include all the views
include: "/views/PoP_parameters.view"
include: "/views/PoP_dimensions.view"

view: page_view {
  derived_table: {

    sql:
      SELECT
        FORMAT_DATE("%Y%m%d", CAST(date AS DATE)) as date,
        MOD(CAST(100*RAND() AS INT64), 100) as pv
      FROM
        UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2022-06-30')) AS date;;
  }

  ... stuff here.... 

  measure: pageview_reference_advanced {
    label: "pageview sum ({% if parameters.select_reference_date._is_filtered %}Reference {% else %}Current {% endif %} {% parameter parameters.select_timeframe_advanced %})"
    type: sum
    sql: ${TABLE}.pv ;;
    filters: [current_vs_previous_period_advanced: "reference"]
  }

  ... stuff here...

}

PoP_dimensions.view:

include: "/views/page_view.view"
include: "/views/PoP_parameters.view"
include: "/views/PoP_timeframes.view"

view: +page_view {

  dimension: current_vs_previous_period_advanced {
    label: "Current vs Previous Period"
    hidden: yes
    description: "Use this dimension alongside \"Select Timeframe\" and \"Select Comparison Type\" Filters to compare a specific timeframe (month, quarter, year) and the corresponding one of the previous year"
    type: string
    sql: ... stuff here ... ;;
  }

  ... more stuff ....
}

hidden or not I have the same error:

do you think the “refinements” is working well here?

1 Like
4

Okay, so I tested it trying to remember a multi-layered refinement I did a while ago. I believe using fields from refinements in the main view is not possible. This is not documented here: https://cloud.google.com/looker/docs/lookml-refinements

There are two ways you can solve it.

  1. Put measures in PoP_measures.view.lkml - include dimensions in there and things should work fine
  2. I usually put dimensions (which are fewer) in the main file (page_view.view in your case) and then use refinements to create derivative dimensions or measures
2 Likes
5

Thanks for your thorough & thoughtful solutions @Dawid !