How to set up an aggregation year filter and switch the data acquisition year for the entire dashboard at once

[takeshi_osawa]

We would like to know if you have any ideas on how to implement the requirements in the following prerequisites.

1. requirements
I would like to set up a single filter on the dashboard to switch the year of aggregation, and then switch the year of aggregation for each Tile at once according to the selected year.

2. prerequisite
In the dashboard, there exists Tile A generated from Explore A and Tile B generated from Explore B.
Explore A refers to TABLE_A and Explore B refers to TABLE_B.
In addition, TABLE_A and TABLE_B have data archived by year, respectively, and the following archive tables exist.

• TABLE_A_2022
• TABLE_A_2021
• TABLE_B_2022
• TABLE_B_2021

3. implementation method considered
Define the aggregation year as a parameter and set up an aggregation year filter on the dashboard.
Switch the reference of sql_table_name according to the selected year.

view: view_A {
    sql_table_name:
        {% if aggregate_year._parameter_value == '2021' %}
            `project_name.dataset_name.TABLE_A_2021`
        {% else if aggregate_year._parameter_value == '2022' %}
            `project_name.dataset_name.TABLE_A_2022`
        {% else %}
            `project_name.dataset_name.TABLE_A`
        {% end if %} ;;

    parameter: aggregate_year {
        type: string
        label: "aggregate year"
        allowed_value: {
            label: "Year 2023"
            value: "2023"
            }
        allowed_value: {
            label: "Year 2022" value: "2022" }
            value: "2022" }
            }
        } allowed_value: {
            label: "Year 2021" value: "2021" }
            value: "2021" }
            }
        }
    }
}

※Define the same parameters for view_B, and make sql_table_name branch according to aggregate_year.

This method was found to be NG in LookML syntax.
When adding a branching process to sql_table_name, if a parameter is involved, it is NG in the specification.

4. reference
The following implementation method does not meet the requirements.
This is because it requires a dedicated filter for view_B, and multiple aggregate year filters must be installed in the dashboard.
The point of this requirement is to use one aggregate year filter to switch the year of Tiles generated from multiple Explore at once.

view: view_A {
    sql_table_name: {% parameter aggregate_year %};;

    parameter: aggregate_year {
        type: unquoted
        label: "aggregate year"
        allowed_value: {
            label: "Year 2023"
            value: "project_name.dataset_name.TABLE_A"
            }
        allowed_value: {
            label: "Year 2022" value: "project_name.dataset_name.
            value: "project_name.dataset_name.TABLE_A_2022" }
            }
        } allowed_value: { label: "Year 2021" value: "project_name.dataset_name.
            label: "Year 2021" value: "project_name.dataset_name.
            value: "project_name.dataset_name.TABLE_A_2021" }
            }
    }