filters and ninja templating on a big number with trend chart #28643
Replies: 3 comments
-
Hi, I came across this by chance and saw your query, to apply Jinja filters you must use the format:
In your dashboard, you should have a filter that contains the 'to_dttm' field. |
Beta Was this translation helpful? Give feedback.
-
Hello @alx25, thanks for your reply. These are the filters I am defining. So how could I use this values in my sql query? Thanks for your kind help |
Beta Was this translation helpful? Give feedback.
-
Is there any solution to this problem? |
Beta Was this translation helpful? Give feedback.
-
I am having some problems creating a chart and was wandering if you guys could help me. I am trying to show the revenue obtained from the new customers for a certain period defined by a filter and compare it with the exact same period but the year before, for that I am using Jinja templating to read from_dttm and to_dttm.
This is the query, i am only getting the customers from a given period and the same period the year before and joining with payments to get the revenue of such customers. the first column is then the beginning of the financial year and the second is the revenue associated to it. Because the type of chart is big number with a trend, it requires a temporal field, i am creating this as a calculated column PARSE_DATETIME("%Y-%m-%d",financial_year). This is the query that i use to create the dataset:
WITH new_customers AS (
SELECT
event_time,
JSON_EXTRACT(payload, "$.reference") AS reference
FROM dihapi.THP.customer_events
WHERE TYPE = "customer_created"
AND JSON_EXTRACT(payload, "$.reference") IS NOT NULL
AND (
FORMAT_DATE('%Y-%m-%d', event_time) BETWEEN SUBSTR('{{ from_dttm | default("2024-02-01T00:00:00") }}', 1, 10) AND SUBSTR('{{ to_dttm | default("2024-02-29T00:00:00") }}', 1, 10)
OR FORMAT_DATE('%Y-%m-%d', event_time) BETWEEN FORMAT_DATE('%Y-%m-%d', DATE_SUB(DATE('{{ from_dttm | default("2024-02-01T00:00:00") }}'), INTERVAL 1 YEAR))
AND FORMAT_DATE('%Y-%m-%d', DATE_SUB(DATE('{{ to_dttm | default("2024-02-29T00:00:00") }}'), INTERVAL 1 YEAR))
)
),
payments AS (
SELECT
JSON_EXTRACT(payload, "$.json.payload.reference") AS reference,
JSON_EXTRACT(payload, "$.json.payload.decimal_amount") AS revenue
FROM dihapi.THP.customer_events
WHERE TYPE = "fat_zebra_payment"
AND JSON_EXTRACT(payload, "$.json.payload.reference") IS NOT NULL
AND JSON_EXTRACT(payload, "$.json.payload.decimal_amount") IS NOT NULL
),
new_patient_rev AS (
SELECT
CASE
WHEN event_time < '2023-07-01' THEN '2022-07-01'
ELSE '2023-07-01'
END AS financial_year,
'New' AS patient,
CAST(revenue AS FLOAT64) AS revenue
FROM new_customers
INNER JOIN payments ON new_customers.reference = payments.reference
)
SELECT financial_year, CAST(SUM(revenue) AS FLOAT64) AS revenue
FROM new_patient_rev
GROUP BY financial_year
-- The problem is that when the filter is applied, the chart runs the following, so the fields in the jinja templating are filled with the default values and the filter is run on the column that indicated the start of the financial year. What i would want is the variables of the jinja templating being filled with the values of the filter. Do you know how to solve this? or maybe re engineer this in another way? Thank you for your kind help
SELECT DATETIME_TRUNC(PARSE_DATETIME("%Y-%m-%d", financial_year), MONTH) AS
date
,max(
revenue
) ASMAX_revenue__b5998
FROM
(WITH new_customers AS
(SELECT event_time,
JSON_EXTRACT(payload, "$.reference") AS reference
FROM dihapi.THP.customer_events
WHERE TYPE = "customer_created"
AND JSON_EXTRACT(payload, "$.reference") IS NOT NULL
AND (FORMAT_DATE('%Y-%m-%d', event_time) BETWEEN SUBSTR('2024-02-01T00:00:00', 1, 10) AND SUBSTR('2024-02-29T00:00:00', 1, 10)
OR FORMAT_DATE('%Y-%m-%d', event_time) BETWEEN FORMAT_DATE('%Y-%m-%d', DATE_SUB(DATE('2024-02-01T00:00:00'), INTERVAL 1 YEAR)) AND FORMAT_DATE('%Y-%m-%d', DATE_SUB(DATE('2024-02-29T00:00:00'), INTERVAL 1 YEAR))) ),
payments AS
(SELECT JSON_EXTRACT(payload, "$.json.payload.reference") AS reference,
JSON_EXTRACT(payload, "$.json.payload.decimal_amount") AS revenue
FROM dihapi.THP.customer_events
WHERE TYPE = "fat_zebra_payment"
AND JSON_EXTRACT(payload, "$.json.payload.reference") IS NOT NULL
AND JSON_EXTRACT(payload, "$.json.payload.decimal_amount") IS NOT NULL ),
new_patient_rev AS
(SELECT CASE
WHEN event_time < '2023-07-01' THEN '2022-07-01'
ELSE '2023-07-01'
END AS financial_year,
'New' AS patient,
CAST(revenue AS FLOAT64) AS revenue
FROM new_customers
INNER JOIN payments ON new_customers.reference = payments.reference) SELECT financial_year,
CAST(SUM(revenue) AS FLOAT64) AS revenue
FROM new_patient_rev
GROUP BY financial_year) AS
virtual_table
WHERE PARSE_DATETIME("%Y-%m-%d", financial_year) >= CAST('2023-07-01T00:00:00.000000' AS DATETIME)
AND PARSE_DATETIME("%Y-%m-%d", financial_year) < CAST('2024-05-23T12:42:52.000000' AS DATETIME)
GROUP BY
date
LIMIT 50000;
Beta Was this translation helpful? Give feedback.
All reactions