I have the following derived query:
derived_table: {
sql:select * from this_year
union all
select * from last_year ;; }
I have the following filter:
filter: this_year_last_year_filter {
label: "This Year Last Year Analysis"
type: string
suggestions: ["No", "Yes"]
}
what I am trying to achieve is, based on what the filter value is selected the derived query should be updated. if the this_year_last_year_filter = Yes then only execute the second part of the union all else just execute the first part of the query.
This is what is expected:
derived_table: {
sql:
select * from this_year
{% if this_year_last_year_filter.value == “Yes”%}
union all
select * from last_year ;;
{% endif %}
}
how to achieve this ? any help would be greatly appreciated.
Hi Riteshm!
I think your sample code is close.
Please find this example :
view: store_current_one_store {
derived_table: {
sql: SELECT
(DATE(store_revenue_current."store_revenue_date" )) AS "store_revenue_current.store_revenue_date",
store_revenue_current."store_number_code" AS "store_revenue_current.store_number_code"
FROM public.store_revenue_current AS store_revenue_current
{% if store_lists._parameter_value != "'all'" %}
WHERE (store_revenue_current."store_number_code" ) = {% parameter store_lists %}
{% endif %}
GROUP BY
1,
2
ORDER BY
1 DESC
;;
}
parameter: store_lists {
type: string
default_value: "all"
allowed_value: {
label: "First Store - 8501"
value: "8501"
}
allowed_value: {
label: "Second Store - 8502"
value: "8502"
}
allowed_value: {
label: "All Stores"
value: "all"
}
}
Best regards!
Leo