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;;
}
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.
Solved! Go to Solution.
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.
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)
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?
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.
Thanks for your thorough & thoughtful solutions @Dawid!