GitHub user sreekanthpamujula9-byte created a discussion: How to create a
Cumulative Target vs. Window-based Actual metric in a single chart using Jinja?
Here is a professionally structured request you can post to the Apache Superset
community (Slack, GitHub Discussions, or Stack Overflow). It explains your
requirement clearly with the technical context they will need to help you.
Title Suggestion
How to create a Cumulative Target vs. Window-based Actual metric in a single
chart using Jinja?
The Request Content
Background/Scenario: I am building a "Livestreaming Performance" chart for my
business, which started in 2022. I have a single Master/Transactional table and
I want to use a single Dashboard Date Filter to drive two different types of
logic in one chart.
The Exact Requirement: I need to compare a "Target" (Capacity) against "Actual"
(Activity).
Metric A (Cumulative Target): This must show the total dealers onboarded from
the start of the business (2022-01-01) up to the Max Date of the dashboard
selection. It must ignore the Start Date of the filter.
Example: If a user selects 2024-01-01 to 2024-01-31, this metric should return
the count of all dealers onboarded since 2022 (e.g., 100 dealers).
Metric B (Window-based Actual): This must show the dealers active strictly
within the selected range.
Example: If a user selects 2024-01-01 to 2024-01-31, this metric should only
return dealers who performed an activity during those 31 days (e.g., 90
dealers).
The Problem: By default, when I select a date range on the dashboard, Superset
applies a global WHERE date >= 'start' AND date <= 'end' clause to the entire
query. This "clips" my Target metric, making it only show dealers onboarded in
2024 and losing my 2022/2023 history.
Proposed Solution / What I've Tried: I am trying to use Custom SQL metrics with
Jinja templates to manually handle the date bounds:
Target Metric SQL: COUNT(DISTINCT CASE WHEN event_date <= '{{ to_dttm }}' THEN
dealer_code END)
Actual Metric SQL: COUNT(DISTINCT CASE WHEN event_date >= '{{ from_dttm }}' AND
event_date <= '{{ to_dttm }}' THEN dealer_code END)
The Question: How do I prevent Superset from adding the automatic WHERE clause
that restricts the dataset to the selected range, so that my "Target" metric
can see the full history back to 2022?
Is setting the Time Column to "None" in the chart's "Data" tab and relying
entirely on Jinja metrics the standard way to do this?
If I set Time Column to None, will the Dashboard Filter still pass the
from_dttm and to_dttm values to my Jinja code?
GitHub link: https://github.com/apache/superset/discussions/37754
----
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]
