- 1 Minute to read
- Print
- DarkLight
- PDF
Defining the External Source Model
- 1 Minute to read
- Print
- DarkLight
- PDF
To define the source model, go to Model > External Source Model > Source Model.
Give the Source Model a name, such as Opportunity Data.
Fill in the fields and formulas as listed below.
Click Save.
Here are all the fields to create in the External Source Model. The first 7 fields match the 7 columns in the source data.
Property | Value |
|
|
|
Source Model | Opportunity Data |
|
|
|
|
|
|
|
|
Field | Type | Format | Expression | Include in Data Load |
Opportunity Name | Text |
|
| Yes |
Sales Segment | Text |
|
| Yes |
Opportunity Owner | Text |
|
| Yes |
Lead Source | Text |
|
| Yes |
Term | Numeric |
|
| Yes |
ARR | Numeric |
|
| Yes |
Service Start | Date | MM/DD/YYYY |
| Yes |
All remaining fields are calculated or preset with constants. In all these fields, Include in Data Load is No. Explanation of the Lookup and Round, If, and DaysFactor functions are provided below.
Field | Type | Format | Expression | Include in Data Load |
Service End | Formula | MM/DD/YYYY | LOOKUP("Opportunity - Daily Calendar" , "Service End" , {Service Start},{Term}) | No |
PlanningStart | ConstantDate | MM/DD/YYYY | 01/01/2018 | No |
PlanningEnd | ConstantDate | MM/DD/YYYY | 12/31/2019 | No |
Jan-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jan-2018")>0.5,[ARR]/12,0),0) | No |
Feb-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Feb-2018")>0.5,[ARR]/12,0),0) | No |
Mar-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Mar-2018")>0.5,[ARR]/12,0),0) | No |
Apr-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Apr-2018")>0.5,[ARR]/12,0),0) | No |
May-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"May-2018")>0.5,[ARR]/12,0),0) | No |
Jun-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jun-2018")>0.5,[ARR]/12,0),0) | No |
Jul-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jul-2018")>0.5,[ARR]/12,0),0) | No |
Aug-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Aug-2018")>0.5,[ARR]/12,0),0) | No |
Sep-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Sep-2018")>0.5,[ARR]/12,0),0) | No |
Oct-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Oct-2018")>0.5,[ARR]/12,0),0) | No |
Nov-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Nov-2018")>0.5,[ARR]/12,0),0) | No |
Dec-18 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Dec-2018")>0.5,[ARR]/12,0),0) | No |
Jan-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jan-2019")>0.5,[ARR]/12,0),0) | No |
Feb-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Feb-2019")>0.5,[ARR]/12,0),0) | No |
Mar-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Mar-2019")>0.5,[ARR]/12,0),0) | No |
Apr-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Apr-2019")>0.5,[ARR]/12,0),0) | No |
May-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"May-2019")>0.5,[ARR]/12,0),0) | No |
Jun-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jun-2019")>0.5,[ARR]/12,0),0) | No |
Jul-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Jul-2019")>0.5,[ARR]/12,0),0) | No |
Aug-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Aug-2019")>0.5,[ARR]/12,0),0) | No |
Sep-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Sep-2019")>0.5,[ARR]/12,0),0) | No |
Oct-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Oct-2019")>0.5,[ARR]/12,0),0) | No |
Nov-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Nov-2019")>0.5,[ARR]/12,0),0) | No |
Dec-19 | Formula |
| ROUND(IF(DAYSFACTOR([PlanningStart],[PlanningEnd],[Service Start],[Service End],"Dec-2019")>0.5,[ARR]/12,0),0) | No |
Scenario | ConstantText |
| Forecast | No |
Account - Sub Revenue | ConstantText |
| Subscription Revenue | No |
The resulting Source Model appears as follows: