How to solve the exceptions when creating a new metric with existing metrics in dataset?

[zanglili]

In dataset，there are two metrics AC and PY like this:
AC:
{% set selected_years = filter_values('year') %}
{% set selected_months = filter_values('month') %}
{% if selected_years | length == 2 %}
{% if selected_years[0] > selected_years[1] %}
{% set ac_year = selected_years[0] %}
{% set py_year = selected_years[1] %}
{% else %}
{% set ac_year = selected_years[1] %}
{% set py_year = selected_years[0] %}
{% endif %}
{% if selected_months %}
SUM(CASE WHEN year = {{ ac_year }} and month = {{ selected_months[0] }} THEN depletion_quantity ELSE 0 END)
{% else %}
SUM(CASE WHEN year = {{ ac_year }} THEN depletion_quantity ELSE 0 END)
{% endif %}
{% else %}
'N/A'
{% endif %}

PY:
{% set selected_years = filter_values('year') %}
{% set selected_months = filter_values('month') %}
{% if selected_years | length == 2 %}
{% if selected_years[0] > selected_years[1] %}
{% set ac_year = selected_years[0] %}
{% set py_year = selected_years[1] %}
{% else %}
{% set ac_year = selected_years[1] %}
{% set py_year = selected_years[0] %}
{% endif %}
{% if selected_months %}
SUM(CASE WHEN year = {{ py_year }} and month = {{ selected_months[0] }} THEN depletion_quantity ELSE 0 END)
{% else %}
SUM(CASE WHEN year = {{ py_year }} THEN depletion_quantity ELSE 0 END)
{% endif %}
{% else %}
'N/A'
{% endif %}

and then I created a new metric YOY with AC and PY:
({{ metric('AC') }} - {{ metric('PY') }})/({{ metric('PY') }} + 1e+1)

My chart used the metric YOY. When update the chart, some exception appeared:

And the query like this:

Any friends have some suggestions? Thanks!

[dosubot[bot]]

Hey @zanglili! I'm here to help you with your technical issues and questions. Let's tackle this together!

To resolve the SQL syntax error when creating a new metric YOY using existing metrics AC and PY in a dataset with Jinja templating, you can use the metric macro to retrieve the SQL expressions for the metrics AC and PY. Here is an example of how you can define the YOY metric using Jinja templating:

{% set AC = metric('AC') %}
{% set PY = metric('PY') %}

({{ AC }} - {{ PY }}) / ({{ PY }} + 1e+1)

This Jinja template will dynamically insert the SQL expressions for the AC and PY metrics into the formula for the YOY metric. The metric macro retrieves the SQL syntax for the given metric key, ensuring that the correct expressions are used in the calculation. Make sure that the ENABLE_TEMPLATE_PROCESSING feature flag is enabled in your superset_config.py to use Jinja templating in your queries ^[1][2].

Additionally, ensure that your Jinja template does not contain any syntax errors and that all variables and functions used are correctly defined and accessible within the context of the query.

To continue talking to Dosu, mention @dosu.

[zanglili]

It not solve the problem, the same exception is still existing.

[zanglili]

Anybody here?
I'm using superset4.0.2.