Add a Forecast Metric

Learning Objectives

After completing this unit, you’ll be able to:

• Introduce actual amount derived from sales agreements into forecast calculations.
• Add a metric by traversing the nodes of a Data Processing Engine (DPE) definition.

Add Sales Agreement Actual Amount

Cindy swiftly moves on to the next requirement: Add the Actual Amount field from sales agreements to forecast calculations. She’s already added the field on the Advanced Account Forecast Fact object and defined it as a measure in the forecast set. She gets started with the definition Generate Account Forecast_AMER Sales Forecasts.

First, she must introduce this new field in all the applicable nodes. Actual Amount is a metric derived from sales agreements and the values are specified for each product within an agreement. So, Cindy starts with the data source node Sales Agreement Product.

1. Open the definition Generate Account Forecast_AMER Sales Forecasts.
2. Click the data source node Sales Agreement Product and click Selected Fields.
3. Select the field Total Actual Amount and click Done.
4. Click Done to save.
5. Click the reference node hyperlink to open the next node.

Next, she follows the trail of reference nodes and adds the field in all the corresponding nodes.

1. On the node Filtered Sales Agreement Join Sales Agreement Product, in the Second Node section, click Selected Fields. 
2. Select TotalActualAmount and click Done.
3. Click Done to save.
4. Click the reference node hyperlink to open the next node, Filtered Sales Agreement Join Sales Agreement Product Schedule.
5. In the First Node section, click Selected Fields.
6. Select TotalActualAmount and click Done.
7. Click Done to save.
8. Click the reference node hyperlink to open the next node.
9. Follow steps 5–8 for the node Valid Sales Agreement Join Upserted Account Forecast Set Partner.
10. The next two nodes don’t require changes. Use the reference links to skip these two nodes.
    • Identify Sales Agreements Within Account Forecast Set Partner Date Range
    • Filter Valid Sales Agreement Within Account Forecast Set Partner Date Range
11. Follow steps 5–8 for the next two nodes.
    • Valid Sales Agreement Within Acct Frcst Set Partner Date Range Join Active Prod
    • Valid Sales Agreement Join Account Forecast Set Partner Period Data

These nodes don’t require any changes. Click the reference node hyperlinks to skip them.

• Identify Valid Period Nodes For SAPS Start Date And End Date
• Filter Valid Period Nodes For SAPS Start Date And End Date
• Compute End Of Last Period And Sales Agreement Effective Date
• Compute Number of Applicable Days For SAPS Quantity Calculations
• Fetch Sales Price From Last Period For Calculation
• Determine Last Period Quantity And Revenue For SAPS Calculation
• Compute Quantity From Applicable Periods In SAPS Period Join Data
• Compute Applicable Quantity And Revenue In SAPS Period Join Data

12. Click Save.

Follow the Trail

Next, Cindy defines an aggregate field to store the cumulative actual amount for a single period.

1. Open the definition Generate Account Forecast_AMER Sales Forecasts.
2. Find and select the node Aggregate Records To Consolidate SAPS Period Join Data.
3. Click Add Aggregate and specify the following:
   • Alias: ConsolidatedTotalActualAmount
   • Select Function: Sum
   • Select Aggregate Field: TotalActualAmount
4. Click Done.
5. Click the reference node hyperlink to open the next node, Sales Agreement Metrics Data Join Account Forecast Set Partner Period Data.
6. In the First Node section, click Selected Fields.
7. Select ConsolidatedTotalActualAmount and click Done.
8. Click Done to save.

Next, Cindy must ensure that the actual amount gets considered when the periods of sales agreements are matched with the periods of an account forecast.

1. Find and select the node Compare Sales Agreement Metric Line Item Close Date With Period Range.
2. Click Add Formula and specify the following:
   • Alias: TotalActualAmountForCurrentPeriod
   • Field Type: Number
   • Length: 16
   • Decimal Places: 2
   • Formula: IF({LeftPeriodId}=={PeriodId}, IF({SalesAgreementEffectiveDate} >= {PeriodStartDate} && {SalesAgreementEffectiveDate} <= {PeriodEndDate}, {ConsolidatedTotalActualAmount}, 0), 0)
3. Click Validate Syntax to verify the formula and click Done.
4. Click the reference node hyperlink to open the next node.

Next, Cindy ensures that for each forecast fact record created or updated for a product-period combination, the total actual amount for all applicable sales agreements is aggregated as a field value.

1. On the node Aggregate SAPS Records To Remove Duplicate For Forecast Facts, click Add Aggregate, and specify the following:
   1. Alias: ComputedTotalActualAmount
   2. Select Function: Sum
   3. Select Aggregate Field: TotalActualAmountForCurrentPeriod
2. Click Done.
3. Click the reference node hyperlink to open the next node.
4. On the node Append Consolidated Opportunity Order SA Metric Data For Forecast Facts, select Unique source nodes.
5. Click the reference node hyperlink to open the next node.
6. On the node Aggregate Account Forecast Fact Records To Eliminate Duplicates, click Add Aggregate, and specify the following:
   1. Alias: CumulativeActualAmount
   2. Select Function: Sum
   3. Select Aggregate Field: ComputedTotalActualAmount
7. Click Done.
8. Click Save.

Add the Alias

Cindy is almost there! She now ensures that the new alias CumulativeActualAmount is available in the next set of referenced nodes.

1. Open the node Generated Account Forecast Facts Join Active Products.
2. In the First Node section, click Selected Fields and select CumulativeActualAmount.
3. Click Done and Done.
4. Click the reference node hyperlink to open the next node.
5. Follow steps 2–4 for the next three nodes:
   1. Generated Account Forecast Facts Join Periods
   2. Generated Account Forecast Facts Join Filtered Accounts
   3. Generated Account Forecast Facts Join Existing Account Forecast Facts
6. No changes are required to the next two nodes.
   1. Sort Before Writeback
   2. Compute IsActive Attribute On Account Forecast Fact
7. Click Save.

Map It Up!

Finally, Cindy maps the alias she’s used to consolidate the actual amount (CumulativeActualAmount) with the custom field (Actual_Amount__c) on the object Advanced Account Forecast Fact. This step ensures that data is written back to the required field.

1. On the node Upsert Account Forecast Fact Record, click Add Row and specify the following:
   • Source Node Field: CumulativeActualAmount
   • Target Field: Actual_Amount__c
2. Click Done.
3. Click Save.

Onward and Upward

Cindy is finally finished and feels a sense of accomplishment, and if you’re following, go ahead and reward yourself. She can now run the updated definitions to check out the new forecast grids. Cindy used two approaches to customize her DPE definitions: JSON and UI. Cindy listened to the needs of her stakeholders and successfully paved the way for more nuanced and flexible forecasting needs.

The true power of Advanced Account Forecasting lies in the power of DPE and the customizations you can make within the definitions to automate calculations. Are you ready to apply the knowledge for your own forecasting requirements?

Resources

• Salesforce Help: Example: Analyze Product Cost and Profit Margins with Advanced Account Forecasting and Data Processing Engine