Big number with threadline ERROR with "cumsum" advanced analytics and filters defined in jinja template

Bug description

Error in Big number with threadline chart if No data with filters defined by jinja template in complex CTE SQL query

How to reproduce the bug

Define complex dataset with CTE and impletement existed filters as jinja template
Create chart Big number with threadline. Set rolling function "cumsum"
Place chart on a dashboard with different related (by jinja) filters.
Select values in related (by jinja) filters to get No data
Result

Screenshots/recordings

No response

Superset version

4.0.2

Python version

Not applicable

Node version

Not applicable

Browser

Chrome

Additional context

No response

Checklist

[ ] I have searched Superset docs and Slack and didn't find a solution to my problem.
[ ] I have searched the GitHub issue tracker and didn't find a similar bug report.
[ ] I have checked Superset's logs for errors and if I found a relevant Python stacktrace, I included it here as text in the "additional context" section.

I found a similar open issue that might be relevant to your problem: Error: Referenced columns not available in DataFrame in Mixed Charts when one query has only nulls. This issue was last updated on August 13, 2024.

To resolve the "Referenced columns not available in DataFrame" error when using Jinja templating for filters in a Big number with trendline chart in Superset version 4.0.2, you need to ensure that the columns referenced in your Jinja template are actually present in the DataFrame being queried. Here are some steps to troubleshoot and resolve the issue:

Verify Column Names: Ensure that the column names used in your Jinja template match exactly with the column names in your DataFrame.
Check Query Result: Make sure that the columns you are referencing in the Jinja template are included in the query result. You can do this by printing or logging the DataFrame columns before applying the template.
Update Jinja Template: Modify your Jinja template to correctly reference the available columns.

Additionally, Superset provides several Jinja context functions that can be used to dynamically access and manipulate data. These functions include url_param, current_user_id, current_username, current_user_email, filter_values, and get_filters. These functions can be used to ensure that your template references the correct columns and values.

Here is an example of how you can modify your Jinja template to ensure it references the correct columns:

from jinja2 import Template

# Example DataFrame
data = {
    'column1': [1, 2, 3],
    'column2': [4, 5, 6]
}
df = pd.DataFrame(data)

# Jinja template
template_str = """
{% if 'column1' in df.columns %}
    {{ df['column1'].sum() }}
{% else %}
    Column 'column1' not found in DataFrame
{% endif %}
"""

# Create a Jinja template
template = Template(template_str)

# Render the template with the DataFrame
result = template.render(df=df)
print(result)

In this example, the Jinja template checks if 'column1' is present in the DataFrame columns before attempting to sum its values. This prevents the error by ensuring that the column exists ^[1]^[2].

To continue talking to Dosu, mention @dosu.

apache / superset