Introduction:
Salesforce provides an OpportunityHistory table which stores an audit trail of all the changes made to an opportunity. Using data modelling we can roll back and obtain the historical state of the pipeline as of any past month quarter or week.
Instructions
#1
Download the ecube and excel file.
#2
Place the Excel file under C:\Users\Public (you can change the location in the Elasticube Manager)
#3
Open the ecube file in the Desktop Elasticube Manager.
#4
Select Elasticube > Change Connectivity Settings and enter your Salesforce credentials.
#5
Edit the FinalCloseDate custom field in the Opportunity table to ensure the appropriate logic for a closed opportunity.
#5
There are 2 tables PipelineMovement and PipelineOppsHistory
PipelineOppsHistory - This table provides a rollback into the OpportunityHistory table for any past Month, Quarter or Week. It provides the last state of the Opportunity for a specific period.
In the Dashboard Widget
Group By - PipelineOppsHistory.PeriodDate
Break By - OpportunityHistory.StageName
Aggregate - Distinct Count PipelineOppsHistory.OpportunityId
PipelineMovement - This table provides the Pipeline events grouped as Existing, Added, Closed Lost, Closed Won for any past Month, Quarter or Week.
In the Dashboard Widget
Group By - PipelineMovement.PeriodDate
Break By - PipelineMovement.Event Type
Aggregate - Distinct Count PipelineMovement.Id
