AnsweredAssumed Answered

Calculating Opportunity Aging from Advance Reports

Question asked by Niegel Leoncio on Jun 3, 2020
Latest reply on Jun 4, 2020 by André Lopes

We wanted to be able to create a report that will calculate opportunity aging without creating a bunch of new fields in Sugar. The idea behind this is I can create an Advanced Report query that would add calculated values for the "time in stage". I came up with the following script but the advanced report UI won't save it and it would say invalid query. Can I get help in figuring out what I need to correct here?

 

SELECT
IFNULL(opportunities.id, '') primaryid,
IFNULL(opportunities.name, '') opportunities_name,
opportunities.date_entered Date_Entered,
opportunities_cstm.territory_c Territory,
IFNULL(opportunities.opportunity_type, '') Type,
IFNULL(opportunities.lead_source, '') Lead_Source,
opportunities.probability Probability,
opportunities.amount Estimated_Value,
IFNULL(opportunities.sales_stage, '') Sales_Stage,
opportunities.date_closed Expected_Close_Date,
opportunities_cstm.actual_close_date_c Actual_Close_Date,
IFNULL(l1.id, '') Account_id,
IFNULL(l1.name, '') Account_Name,
l1_cstm.customer_class_c Customer_Class,
GETDATE() RunDate, //This is to capture when the report was ran
l1_cstm.customer_class_description_c Customer_Class_Desc,
opportunities_cstm.total_age_in_days_c Age_In_Days,
opportunities_cstm.timestamp_status_champion_c Champion_Date,
DATEDIFF(DD,opportunities_cstm.timestamp_status_champion_c,GETDATE()) as Champion_Age, //This is to formula to calculate days between champion status time stamp and RunDate
opportunities_cstm.timestamp_status_closed_c Close_Date,
opportunities_cstm.timestamp_status_discovery_c Discovery_Date,
opportunities_cstm.timestamp_status_evaluation_c Evaluation_Date,
opportunities_cstm.timestamp_status_negotiation_c Negotiation_Date,
opportunities_cstm.timestamp_status_qualify_c Qualification_Date,
opportunities_cstm.timestamp_status_stakeholder_c Stakeholders_Date,
opportunities_cstm.timestamp_status_won_c Closed_Won_Date
FROM
opportunities
LEFT JOIN
accounts_opportunities l1_1
ON opportunities.id = l1_1.opportunity_id
AND l1_1.deleted = 0
LEFT JOIN
accounts l1
ON l1.id = l1_1.account_id
AND l1.deleted = 0
LEFT JOIN
opportunities_cstm opportunities_cstm
ON opportunities.id = opportunities_cstm.id_c
LEFT JOIN
accounts_cstm l1_cstm
ON l1.id = l1_cstm.id_c
WHERE
(
((opportunities.date_closed > '2020-01-01' )
AND
(
opportunities.date_entered < '2020-05-04 07:00:00'
)
)
)
AND opportunities.deleted = 0

Outcomes