GitHub user dosubot[bot] added a comment to the discussion: How to create a
Cumulative Target vs. Window-based Actual metric in a single chart using Jinja?
<!-- Answer -->
Here are the complete steps to set up a Superset chart comparing cumulative
Target vs. window-based Actual metrics using Jinja templating:
1. **Enable Jinja Templating**
Make sure your Superset instance has Jinja templating enabled. In your
`superset_config.py`, set:
```python
FEATURE_FLAGS = {
"ENABLE_TEMPLATE_PROCESSING": True,
}
```
This lets you use Jinja in virtual datasets and custom SQL metrics
[source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/docs/docs/configuration/sql-templating.mdx#L13-L463).
2. **Create or Edit Your Dataset**
Go to Data → Datasets, and either create a new virtual dataset or edit your
existing one.
Use the SQL Lab or Dataset editor to write custom SQL with Jinja for your
metrics.
3. **Write Jinja SQL for Both Metrics**
- **Cumulative Target (ignores filter start date):**
```sql
COUNT(DISTINCT CASE WHEN onboard_date <= '{{ to_dttm }}' THEN dealer_code
END)
```
Or, for more robust handling and to avoid double filtering:
```jinja
{% set tf = get_time_filter('onboard_date', remove_filter=True) %}
COUNT(DISTINCT CASE WHEN onboard_date <= {{ tf.to_expr }} THEN dealer_code
END)
```
- **Window-based Actual (respects filter range):**
```sql
COUNT(DISTINCT CASE WHEN activity_date >= '{{ from_dttm }}' AND
activity_date <= '{{ to_dttm }}' THEN dealer_code END)
```
Or, using the macro:
```jinja
{% set tf = get_time_filter('activity_date', remove_filter=True) %}
COUNT(DISTINCT CASE WHEN activity_date >= {{ tf.from_expr }} AND
activity_date <= {{ tf.to_expr }} THEN dealer_code END)
```
The `get_time_filter` macro ensures the dashboard filter is only applied
inside your metric, not by Superset's automatic WHERE clause
[source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py#L434-L774).
4. **Configure the Chart**
- In the chart's Data tab, set the **Time Column** to `None`.
This disables Superset's automatic time filtering, so only your Jinja
logic applies
[source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/docs/docs/configuration/sql-templating.mdx#L13-L463).
- Add your custom metrics (Target and Actual) to the chart.
5. **Add and Scope the Dashboard Filter**
- Add a dashboard time filter (native filter) and set its scope to include
your chart.
- The filter will pass `from_dttm` and `to_dttm` values to your Jinja code,
even with Time Column set to None
[source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py#L434-L774).
6. **Best Practices & Troubleshooting**
- Always use `remove_filter=True` in `get_time_filter` to avoid double
filtering.
- Keep filter removal logic at the top level of your dataset SQL (not in
nested datasets).
- If your chart shows no data, check for empty filters and use Jinja if/else
logic to provide defaults.
- Only one main dashboard time filter is supported per chart; for more
complex scenarios, consider a database view that unpivots date columns
[source](https://github.com/apache/superset/discussions/36846#discussioncomment-15352104).
**Example Combined SQL (for a table chart):**
```jinja
SELECT
COUNT(DISTINCT CASE WHEN onboard_date <= {{ get_time_filter('onboard_date',
remove_filter=True).to_expr }} THEN dealer_code END) AS cumulative_target,
COUNT(DISTINCT CASE WHEN activity_date >= {{ get_time_filter('activity_date',
remove_filter=True).from_expr }} AND activity_date <= {{
get_time_filter('activity_date', remove_filter=True).to_expr }} THEN
dealer_code END) AS window_actual
FROM your_table
```
Let me know if you want help with a specific chart type or dataset structure!
<!-- Dosu Comment Footer -->
*To reply, just mention [@dosu](https://go.dosu.dev/dosubot).*
---
<sup>How did I do?
[Good](https://app.dosu.dev/response-feedback/3ef4add2-d690-49fd-99ac-bc1c78801b51?feedback_type=great_response)
|
[Irrelevant](https://app.dosu.dev/response-feedback/3ef4add2-d690-49fd-99ac-bc1c78801b51?feedback_type=irrelevant_answer)
|
[Incorrect](https://app.dosu.dev/response-feedback/3ef4add2-d690-49fd-99ac-bc1c78801b51?feedback_type=incorrect_sources)
|
[Verbose](https://app.dosu.dev/response-feedback/3ef4add2-d690-49fd-99ac-bc1c78801b51?feedback_type=too_verbose)
|
[Hallucination](https://app.dosu.dev/response-feedback/3ef4add2-d690-49fd-99ac-bc1c78801b51?feedback_type=hallucination)
| [Report
🐛](https://app.dosu.dev/response-feedback/3ef4add2-d690-49fd-99ac-bc1c78801b51?feedback_type=bug_report)
|
[Other](https://app.dosu.dev/response-feedback/3ef4add2-d690-49fd-99ac-bc1c78801b51?feedback_type=other)</sup>
[](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=Here%20are%20the%20complete%20steps%20to%20set%20up%20a%20Superset%20chart%20comparing%20cumulative%20Target%20vs.%20window-based%20Actual%20metrics%20using%20Jinja%20templating%3A%0A%0A1.%20%2A%2AEnable%20Jinja%20Templating%2A%2A%20%20%0A%20%20%20Make%20sure%20your%20Superset%20instance%20has%20Jinja%20templating%20enabled.%20In%20your%20%60superset_config.py%60%2C%20set%3A%0A%20%20%20%60%60%60python%0A%20%20%20FEATURE_FLAGS%20%3D%20%7B%0A%20%20%20%20%20%20%20%22ENABLE_TEMPLATE_PROCESSING%22%3A%20True%2C%0A%20%20%20%7D%0A%20%20%20%60%60%60%0A%20%20%20This%20lets%20you%20use%20Jinja%20in%20virtual%20datasets%20and%20custom%20SQL%20metrics%20%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/docs/docs/conf
iguration/sql-templating.mdx%23L13-L463%29.%0A%0A2.%20%2A%2ACreate%20or%20Edit%20Your%20Dataset%2A%2A%20%20%0A%20%20%20Go%20to%20Data%20%E2%86%92%20Datasets%2C%20and%20either%20create%20a%20new%20virtual%20dataset%20or%20edit%20your%20existing%20one.%20%20%0A%20%20%20Use%20the%20SQL%20Lab%20or%20Dataset%20editor%20to%20write%20custom%20SQL%20with%20Jinja%20for%20your%20metrics.%0A%0A3.%20%2A%2AWrite%20Jinja%20SQL%20for%20Both%20Metrics%2A%2A%20%20%0A%20%20%20-%20%2A%2ACumulative%20Target%20%28ignores%20filter%20start%20date%29%3A%2A%2A%0A%20%20%20%20%20%60%60%60sql%0A%20%20%20%20%20COUNT%28DISTINCT%20CASE%20WHEN%20onboard_date%20%3C%3D%20%27%7B%7B%20to_dttm%20%7D%7D%27%20THEN%20dealer_code%20END%29%0A%20%20%20%20%20%60%60%60%0A%20%20%20%20%20Or%2C%20for%20more%20robust%20handling%20and%20to%20avoid%20double%20filtering%3A%0A%20%20%20%20%20%60%60%60jinja%0A%20%20%20%20%20%7B%25%20set%20tf%20%3D%20get_time_filter%28%27onboard_date%27%2C%20remove_filter%3DTrue%29%20%25%7D%0A%20%20%20%2
0%20COUNT%28DISTINCT%20CASE%20WHEN%20onboard_date%20%3C%3D%20%7B%7B%20tf.to_expr%20%7D%7D%20THEN%20dealer_code%20END%29%0A%20%20%20%20%20%60%60%60%0A%20%20%20-%20%2A%2AWindow-based%20Actual%20%28respects%20filter%20range%29%3A%2A%2A%0A%20%20%20%20%20%60%60%60sql%0A%20%20%20%20%20COUNT%28DISTINCT%20CASE%20WHEN%20activity_date%20%3E%3D%20%27%7B%7B%20from_dttm%20%7D%7D%27%20AND%20activity_date%20%3C%3D%20%27%7B%7B%20to_dttm%20%7D%7D%27%20THEN%20dealer_code%20END%29%0A%20%20%20%20%20%60%60%60%0A%20%20%20%20%20Or%2C%20using%20the%20macro%3A%0A%20%20%20%20%20%60%60%60jinja%0A%20%20%20%20%20%7B%25%20set%20tf%20%3D%20get_time_filter%28%27activity_date%27%2C%20remove_filter%3DTrue%29%20%25%7D%0A%20%20%20%20%20COUNT%28DISTINCT%20CASE%20WHEN%20activity_date%20%3E%3D%20%7B%7B%20tf.from_expr%20%7D%7D%20AND%20activity_date%20%3C%3D%20%7B%7B%20tf.to_expr%20%7D%7D%20THEN%20dealer_code%20END%29%0A%20%20%20%20%20%60%60%60%0A%20%20%20The%20%60get_time_filter%60%20macro%20ensures%20the%20dashboard%20fi
lter%20is%20only%20applied%20inside%20your%20metric%2C%20not%20by%20Superset%27s%20automatic%20WHERE%20clause%20%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py%23L434-L774%29.%0A%0A4.%20%2A%2AConfigure%20the%20Chart%2A%2A%20%20%0A%20%20%20-%20In%20the%20chart%27s%20Data%20tab%2C%20set%20the%20%2A%2ATime%20Column%2A%2A%20to%20%60None%60.%20%20%0A%20%20%20%20%20This%20disables%20Superset%27s%20automatic%20time%20filtering%2C%20so%20only%20your%20Jinja%20logic%20applies%20%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/docs/docs/configuration/sql-templating.mdx%23L13-L463%29.%0A%20%20%20-%20Add%20your%20custom%20metrics%20%28Target%20and%20Actual%29%20to%20the%20chart.%0A%0A5.%20%2A%2AAdd%20and%20Scope%20the%20Dashboard%20Filter%2A%2A%20%20%0A%20%20%20-%20Add%20a%20dashboard%20time%20filter%20%28native%20filter%29%20and%20set%20its%20scope%20to%20include%20your%
20chart.%0A%20%20%20-%20The%20filter%20will%20pass%20%60from_dttm%60%20and%20%60to_dttm%60%20values%20to%20your%20Jinja%20code%2C%20even%20with%20Time%20Column%20set%20to%20None%20%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py%23L434-L774%29.%0A%0A6.%20%2A%2ABest%20Practices%20%26%20Troubleshooting%2A%2A%0A%20%20%20-%20Always%20use%20%60remove_filter%3DTrue%60%20in%20%60get_time_filter%60%20to%20avoid%20double%20filtering.%0A%20%20%20-%20Keep%20filter%20removal%20logic%20at%20the%20top%20level%20of%20your%20dataset%20SQL%20%28not%20in%20nested%20datasets%29.%0A%20%20%20-%20If%20your%20chart%20shows%20no%20data%2C%20check%20for%20empty%20filters%20and%20use%20Jinja%20if/else%20logic%20to%20provide%20defaults.%0A%20%20%20-%20Only%20one%20main%20dashboard%20time%20filter%20is%20supported%20per%20chart%3B%20for%20more%20complex%20scenarios%2C%20consider%20a%20database%20view%20that%20unpivots%20date%20columns%2
0%5Bsource%5D%28https%3A//github.com/apache/superset/discussions/36846%23discussioncomment-15352104%29.%0A%0A%2A%2AExample%20Combined%20SQL%20%28for%20a%20table%20chart%29%3A%2A%2A%0A%60%60%60jinja%0ASELECT%0A%20%20COUNT%28DISTINCT%20CASE%20WHEN%20onboard_date%20%3C%3D%20%7B%7B%20get_time_filter%28%27onboard_date%27%2C%20remove_filter%3DTrue%29.to_expr%20%7D%7D%20THEN%20dealer_code%20END%29%20AS%20cumulative_target%2C%0A%20%20COUNT%28DISTINCT%20CASE%20WHEN%20activity_date%20%3E%3D%20%7B%7B%20get_time_filter%28%27activity_date%27%2C%20remove_filter%3DTrue%29.from_expr%20%7D%7D%20AND%20activity_date%20%3C%3D%20%7B%7B%20get_time_filter%28%27activity_date%27%2C%20remove_filter%3DTrue%29.to_expr%20%7D%7D%20THEN%20dealer_code%20END%29%20AS%20window_actual%0AFROM%20your_table%0A%60%60%60%0A%0ALet%20me%20know%20if%20you%20want%20help%20with%20a%20specific%20chart%20type%20or%20dataset%20structure%21) [](https://go.dosu.dev/discord-bot) [](https://twitter.com/intent/tweet?text=%40dosu_ai%20helped%20me%20solve%20this%20issue!&url=https%3A//github.com/apache/superset/discussions/37754)
GitHub link:
https://github.com/apache/superset/discussions/37754#discussioncomment-15717777
----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
