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 --> To compare a cumulative Target metric (ignoring the dashboard filter's start date) vs. a window-based Actual metric (respecting the filter range) in a single Superset chart using Jinja, set the Time Column to "None" in the chart's Data tab. This disables Superset's automatic global WHERE clause, so you can fully control date filtering in your custom SQL metrics with Jinja. The dashboard filter will still pass from_dttm and to_dttm values to your Jinja code, even when Time Column is None [source](https://github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063ffe45377799c/superset/jinja_context.py#L434-L1040). For your metrics, use Jinja like: ```sql -- Cumulative Target COUNT(DISTINCT CASE WHEN onboard_date <= '{{ to_dttm }}' THEN dealer_code END) -- Window-based Actual COUNT(DISTINCT CASE WHEN activity_date >= '{{ from_dttm }}' AND activity_date <= '{{ to_dttm }}' THEN dealer_code END) ``` Or, for more robust handling and to avoid double filtering, use the get_time_filter 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) ``` You can combine both metrics in a single chart or dataset, and each will respond to the dashboard filter as you intend. This approach is standard for advanced date logic in Superset [source](https://github.com/apache/superset/discussions/36846#discussioncomment-15352104). Best practices: enable Jinja templating (`ENABLE_TEMPLATE_PROCESSING = True`), keep filter removal logic at the top level of your dataset, and be aware that only one main dashboard time filter is supported per chart. For more complex scenarios (like multiple date columns or metrics), consider using a database view or UNION queries [source](https://github.com/apache/superset/discussions/36846#discussioncomment-15352104). Let me know if you want a step-by-step setup or have questions about combining metrics in a single query. <!-- 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/042ebb63-739a-4fab-a6d5-8086d4d124e8?feedback_type=great_response) | [Irrelevant](https://app.dosu.dev/response-feedback/042ebb63-739a-4fab-a6d5-8086d4d124e8?feedback_type=irrelevant_answer) | [Incorrect](https://app.dosu.dev/response-feedback/042ebb63-739a-4fab-a6d5-8086d4d124e8?feedback_type=incorrect_sources) | [Verbose](https://app.dosu.dev/response-feedback/042ebb63-739a-4fab-a6d5-8086d4d124e8?feedback_type=too_verbose) | [Hallucination](https://app.dosu.dev/response-feedback/042ebb63-739a-4fab-a6d5-8086d4d124e8?feedback_type=hallucination) | [Report 🐛](https://app.dosu.dev/response-feedback/042ebb63-739a-4fab-a6d5-8086d4d124e8?feedback_type=bug_report) | [Other](https://app.dosu.dev/response-feedback/042ebb63-739a-4fab-a6d5-8086d4d124e8?feedback_type=other)</sup> [](https://app.dosu.dev/a28d3c7e-a9d3-459e-9fb6-3a6f9ff4f357/ask?utm_source=github) [](https://cursor.com/link/prompt?text=To%20compare%20a%20cumulative%20Target%20metric%20%28ignoring%20the%20dashboard%20filter%27s%20start%20date%29%20vs.%20a%20window-based%20Actual%20metric%20%28respecting%20the%20filter%20range%29%20in%20a%20single%20Superset%20chart%20using%20Jinja%2C%20set%20the%20Time%20Column%20to%20%22None%22%20in%20the%20chart%27s%20Data%20tab.%20This%20disables%20Superset%27s%20automatic%20global%20WHERE%20clause%2C%20so%20you%20can%20fully%20control%20date%20filtering%20in%20your%20custom%20SQL%20metrics%20with%20Jinja.%20The%20dashboard%20filter%20will%20still%20pass%20from_dttm%20and%20to_dttm%20values%20to%20your%20Jinja%20code%2C%20even%20when%20Time%20Column%20is%20None%20%5Bsource%5D%28https%3A//github.com/apache/superset/blob/829e4d92d91ceae4b43b1ed3b063 ffe45377799c/superset/jinja_context.py%23L434-L1040%29.%0A%0AFor%20your%20metrics%2C%20use%20Jinja%20like%3A%0A%0A%60%60%60sql%0A--%20Cumulative%20Target%0ACOUNT%28DISTINCT%20CASE%20WHEN%20onboard_date%20%3C%3D%20%27%7B%7B%20to_dttm%20%7D%7D%27%20THEN%20dealer_code%20END%29%0A%0A--%20Window-based%20Actual%0ACOUNT%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%60%60%60%0A%0AOr%2C%20for%20more%20robust%20handling%20and%20to%20avoid%20double%20filtering%2C%20use%20the%20get_time_filter%20macro%3A%0A%0A%60%60%60jinja%0A%7B%25%20set%20tf%20%3D%20get_time_filter%28%27activity_date%27%2C%20remove_filter%3DTrue%29%20%25%7D%0ACOUNT%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%60%60%60%0A%0AYou%20can%20combine%20both%20metrics% 20in%20a%20single%20chart%20or%20dataset%2C%20and%20each%20will%20respond%20to%20the%20dashboard%20filter%20as%20you%20intend.%20This%20approach%20is%20standard%20for%20advanced%20date%20logic%20in%20Superset%20%5Bsource%5D%28https%3A//github.com/apache/superset/discussions/36846%23discussioncomment-15352104%29.%0A%0ABest%20practices%3A%20enable%20Jinja%20templating%20%28%60ENABLE_TEMPLATE_PROCESSING%20%3D%20True%60%29%2C%20keep%20filter%20removal%20logic%20at%20the%20top%20level%20of%20your%20dataset%2C%20and%20be%20aware%20that%20only%20one%20main%20dashboard%20time%20filter%20is%20supported%20per%20chart.%20For%20more%20complex%20scenarios%20%28like%20multiple%20date%20columns%20or%20metrics%29%2C%20consider%20using%20a%20database%20view%20or%20UNION%20queries%20%5Bsource%5D%28https%3A//github.com/apache/superset/discussions/36846%23discussioncomment-15352104%29.%0A%0ALet%20me%20know%20if%20you%20want%20a%20step-by-step%20setup%20or%20have%20questions%20about%20combining%20metric s%20in%20a%20single%20query.) [](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-15717645 ---- 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]
