Using multiple variables within one question/query

flo92 · November 20, 2019, 5:09pm

Hi there! I’m pretty new to Metabase so I’m not sure if this is something that already been brought up. I’m trying to independently filter two metrics that run on different timeframes within the same question/query but I can’t find a way to do it. For the context, I’m trying to subtract two different metrics at any given period. Both have to be filtered by different timeframes (one on contract end date and the other one on close date).

Is this something Metabase can handle at the moment? Would love your suggestions…

Thanks,

flamber · November 20, 2019, 7:09pm

Hi @flo92
I’m not sure I understand what you’re asking. Could you explain in more details - perhaps also add a screenshot? Also, which version of Metabase?
You can filter two columns independently from each other.

flo92 · November 20, 2019, 7:47pm

Hi @flamber

As you can see on the screenshot below, I wrote a sql query that return two different columns, Metric 1 and Metric 2. I would like to filter Metric 1 on created_at and Metric 2 on completed_at within the same view. Let me know if you need more details…

flamber · November 20, 2019, 8:03pm

@flo92
Without seeing the query, it’s difficult to know what’s possible, but if you’re returning all 4 columns (2 metrics, 2 dates), then that shouldn’t be a problem.
But I don’t see your date columns - or are those supposed to be filters?
I still don’t understand what the question is.

flo92 · November 21, 2019, 2:28pm

@flamber yes the two timeframes are supposed to be filtered. I would like to use the variables to filter those two metrics on different timeframes. See the query below… let me know if that help.

WITH sfdc_revenue AS (SELECT
name,
id,
account_id,
stage_name,
opportunity_type_c,
lead_source,
lead_channel_c,
amount as "Opportunity Total Contract Value",
created_date,
close_date,
is_deleted,
net_new_mrr_c as "Net New MRR",
weighted_net_new_mrr_c,
weighted_new_mrr_c,
weighted_added_arr_c,
added_arr_c as "Added ARR",
arr_c as "Opportunity ARR",
monthly_recurring_revenue_mrr_c as "MRR",
resulting_mrr_c,
campaign_id,
owner_id,
contract_start_date_c,
contract_end_date_c,
system_modstamp,
fiscal_quarter,
prior_contract_end_date_auto_c,
master_opportunity_c,
prior_contract_mrr_c,
probability,
contract_length_exact_c,
client_value_perception_c,
Renewal_Forecast_c,
CASE
  WHEN stage_name = 'Closed Won' AND opportunity_type_c in ('Renewal', 'Interim (for delayed Renewals)') AND monthly_recurring_revenue_mrr_c < prior_contract_mrr_c THEN 'Renewal W/ Downsale'
  WHEN stage_name = 'Closed Won' AND opportunity_type_c in ('Renewal', 'Interim (for delayed Renewals)') AND added_arr_c < 0 THEN 'Renewal W/ Downsale added arr'
  WHEN stage_name = 'Closed Won' AND opportunity_type_c in ('Renewal', 'Interim (for delayed Renewals)') AND monthly_recurring_revenue_mrr_c = prior_contract_mrr_c THEN 'Straight Renewal'
  WHEN Renewal_Forecast_c = 'Expected Renewal' AND opportunity_type_c in ( 'Renewal', 'Interim (for delayed Renewals)') AND monthly_recurring_revenue_mrr_c < prior_contract_mrr_c THEN 'Expected Renewal W/ Downsale'
  WHEN Renewal_Forecast_c = 'Expected Renewal' AND opportunity_type_c in ( 'Renewal', 'Interim (for delayed Renewals)') AND monthly_recurring_revenue_mrr_c = prior_contract_mrr_c THEN 'Expected Straight Renewal'
  WHEN Renewal_Forecast_c = 'Expected Renewal' AND opportunity_type_c in ( 'Renewal', 'Interim (for delayed Renewals)') AND monthly_recurring_revenue_mrr_c > prior_contract_mrr_c THEN 'Expected Renewal W/ Upsell'
  WHEN Renewal_Forecast_c = 'Expected Churn' AND opportunity_type_c in ( 'Renewal', 'Interim (for delayed Renewals)') THEN 'Expected Churn'
ELSE NULL
END AS "Renewal_Type"
FROM salesforce.opportunity
)
SELECT
	((COALESCE(SUM(CASE WHEN (sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Renewal w/ Upsell') AND (sfdc_revenue.stage_name ILIKE 'Closed Lost') THEN CASE WHEN resulting_mrr_c >= prior_contract_mrr_c THEN resulting_mrr_c
ELSE prior_contract_mrr_c END  ELSE NULL END), 0))+(COALESCE(SUM(CASE WHEN (sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Renewal w/ Upsell') AND (sfdc_revenue.renewal_forecast_c ILIKE 'Expected Churn') AND (sfdc_revenue.stage_name ILIKE 'negotiation' OR sfdc_revenue.stage_name ILIKE 'proposal presented' OR sfdc_revenue.stage_name ILIKE 'verbal' OR sfdc_revenue.stage_name ILIKE 'in contract') THEN sfdc_revenue.prior_contract_mrr_c   ELSE NULL END), 0))) - (COALESCE(SUM(CASE WHEN ((sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Interim (for delayed Renewals)')) AND (((sfdc_revenue."Renewal_Type") ILIKE 'Renewal W/ Downsale')) THEN sfdc_revenue."added arr"/12  ELSE NULL END), 0))- (COALESCE(SUM(CASE WHEN ((sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Interim (for delayed Renewals)')) AND (((sfdc_revenue."Renewal_Type") ILIKE 'Renewal W/ Downsale added arr')) THEN sfdc_revenue."added arr"/12  ELSE NULL END), 0))-(COALESCE(SUM(CASE WHEN ((sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Interim (for delayed Renewals)')) AND (((sfdc_revenue."Renewal_Type") ILIKE 'Expected Renewal W/ Downsale')) THEN sfdc_revenue."added arr"/12  ELSE NULL END), 0))  AS "sfdc_revenue.gross_mrr_churn",
 	COALESCE(SUM(CASE WHEN (sfdc_revenue.stage_name ILIKE 'Closed Won') AND ((sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'New' OR sfdc_revenue.opportunity_type_c ILIKE 'Interim (for delayed Renewals)' OR sfdc_revenue.opportunity_type_c ILIKE 'Renewal w/ Upsell' OR sfdc_revenue.opportunity_type_c ILIKE 'Expansion')) THEN sfdc_revenue."MRR"  ELSE NULL END), 0) AS "sfdc_revenue.up_for_renewal_mrr"
FROM sfdc_revenue

flamber · November 21, 2019, 2:41pm

@flo92
I don’t see a WHERE in your query - so you need to add that and the columns you want to filter.
There’s a good introduction to SQL filters in the documentation:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

flo92 · November 22, 2019, 4:51pm

@flamber- I added the WHERE statement and a timestamp filter as you mentioned above but it looks like I can only have one field filter for the overall query.

I’m trying to filter this part below with prior_contract_end_date:

SELECT
	((COALESCE(SUM(CASE WHEN (sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Renewal w/ Upsell') AND (sfdc_revenue.stage_name ILIKE 'Closed Lost') THEN CASE WHEN resulting_mrr_c >= prior_contract_mrr_c THEN resulting_mrr_c
ELSE prior_contract_mrr_c END  ELSE NULL END), 0))+(COALESCE(SUM(CASE WHEN (sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Renewal w/ Upsell') AND (sfdc_revenue.renewal_forecast_c ILIKE 'Expected Churn') AND (sfdc_revenue.stage_name ILIKE 'negotiation' OR sfdc_revenue.stage_name ILIKE 'proposal presented' OR sfdc_revenue.stage_name ILIKE 'verbal' OR sfdc_revenue.stage_name ILIKE 'in contract') THEN sfdc_revenue.prior_contract_mrr_c   ELSE NULL END), 0))) - (COALESCE(SUM(CASE WHEN ((sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Interim (for delayed Renewals)')) AND (((sfdc_revenue."Renewal_Type") ILIKE 'Renewal W/ Downsale')) THEN sfdc_revenue."added arr"/12  ELSE NULL END), 0))- (COALESCE(SUM(CASE WHEN ((sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Interim (for delayed Renewals)')) AND (((sfdc_revenue."Renewal_Type") ILIKE 'Renewal W/ Downsale added arr')) THEN sfdc_revenue."added arr"/12  ELSE NULL END), 0))-(COALESCE(SUM(CASE WHEN ((sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'Interim (for delayed Renewals)')) AND (((sfdc_revenue."Renewal_Type") ILIKE 'Expected Renewal W/ Downsale')) THEN sfdc_revenue."added arr"/12  ELSE NULL END), 0))  AS "sfdc_revenue.gross_mrr_churn"

and this part with contract_end_date:

COALESCE(SUM(CASE WHEN (sfdc_revenue.stage_name ILIKE 'Closed Won') AND ((sfdc_revenue.opportunity_type_c ILIKE 'Renewal' OR sfdc_revenue.opportunity_type_c ILIKE 'New' OR sfdc_revenue.opportunity_type_c ILIKE 'Interim (for delayed Renewals)' OR sfdc_revenue.opportunity_type_c ILIKE 'Renewal w/ Upsell' OR sfdc_revenue.opportunity_type_c ILIKE 'Expansion')) THEN sfdc_revenue."MRR" ELSE NULL END), 0) AS "sfdc_revenue.up_for_renewal_mrr"

flamber · November 23, 2019, 3:22am

@flo92
You can have multiple Field Filters (or simple filters) in a query.
But to have independent results based on different filters, you would need a sub-query for each filter.
To make it easier, you should just try to make your query without any Metabase filters. Then when it’s working as expected, then you just replace your WHERE ... with the Metabase filters.