@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