Salesforce: Forecast with Opportunity Split

This is the continuation of previous blog Setup Forecast Quota, in this blog, we will share about Forecast with Opportunity Split Overlay.

1. Opportunity Team and Opportunity Splits

Here is the scenario:
Two users, both users have been enabled for Forecast
1. Johan Forecast is the manager
2. George Mann report to Johan Forecast

Two closed-won opportunities with Overlay split:

opportunity #1

opportunity #2

2. Forecast
Let us the result see in the Forecast tab:

Highlight legend:
- Yellow: this is quota as we discussed in the previous blog
- Pink: this is the forecast item for Closed forecast
- Green: this is summary from all subordinates, including the manager
- Blue: USD 305,500.00 comes from opportunity #2 which is the total amount for the 2nd and 3rd; USD 75,000.00 is from opportunity #1. 


3. SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db2v000005aTHcCAM'

Summary:

• The Forecast Amount in ForecastingItem is auto-populated from Opportunity Split Amount based on the Forecast Type and Forecast Category.
• The Period Id in ForecastingItem is auto-populated based on Opportunity Closed Date.
• Each user will only have 1 line for ForecastingItem in a period for a Forecast Type.
• If you have multi-currencies enabled, only 1 currency will be returned in the query result, this currency is based on defined Corporate Currency.
• The Forecast Amount from ForecastingItem in SOQL is roll-up to the manager, and managers numbers will roll-up to the managers' manager.

Forecast Manager in Forecast Hierarchy

Sample:

There are 2 users under Staff 1: Song Lee & Free Man

Forecast tab

SOQL
SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db0k000000076kCAA' ORDER BY ForecastAmount

What will happen if we remove Maria Ann as Forecast Manager for the role hierarchy General Manager?

Forecast tab

Notice:
1. The Amount is no longer rollup from Maria Ann subordinates'. Forecast managers see forecast rollups from users below them in the forecast hierarchy.
2. Click the arrow next to label 'Maria Ann' will not drill down to the subordinates level.
3. Jack Bob numbers are only from Linda Yie as Maria Ann not contributing anything.
4. The number from Song Lee and Free Man are not roll-up to anyone.

SOQL

SELECT Id, OwnerId, ForecastAmount, Owner.Name, PeriodId, ForecastingTypeId FROM ForecastingItem WHERE ForecastCategoryName = 'Closed' AND ForecastingTypeId = '0Db0k000000076kCAA' ORDER BY ForecastAmount

If Maria Ann have her owned Opportunity Splits

Same for SOQL result, Maria Ann will have USD 18,000.00 for the Forecast Amount.


Now, let us remove Allow Forecasting from Maria Ann

Maria Ann does not appear in the Forecast tab at all.

SOQL:

Remember that Maria Ann still owned Opportunity Split, but she does not appear in both Forecast and SOQL.


Summary:
1. Allow Forecasting is a must for all forecast users.
2. Each manager role in the forecast hierarchy should have a user assigned as the Forecast Manager.
3. The API name for "Allow Forecasting" is ForecastEnabled, you can mass update it with API.

Salesforce: Setup Forecast Quota

1. Enable Opportunity Team

  

2. Enable Opportunity Split and Add Opportunity Split Type (optional)

For this blog, I am adding Overlay opportunity split type

 3. Enable Forecast, Add Forecast Type (optional), and Enable Quota

enable forecast

adding forecast type 

You may need to refresh your web browser to see the “Forecasts Quotas” tab to appear.

4. Enable user for “Allow Forecasting”

From the user detail page or Forecast Hierarchy.

user detail

forecast hierarchy

  

 5. Assign Quotas to Users

Make sure you select the right Forecast Type and select the right currency (if necessary)

Or use the Data Loader tool to load CSV into Forecasting Quota object

6. Set Forecast Manager for each Role Hierarchy

This is only necessary if user have subordinates as per role hierarchy, set up this from Forecast hierarchy.

7. Verify Quota

7A. Forecast Tab

Make sure to select correct Period, Forecast Type, and Currency

7B. SOQL

As mentioned in (5), we can use Data loader to load quota to Forecast Quota object. So, the same we can query from Forecast Quota object too.

SELECT Id, QuotaOwnerId, QuotaAmount, QuotaOwner.Name, ForecastingTypeId, PeriodId FROM ForecastingQuota

• From the above query, this shows us that Quota Amount numbers are NOT roll-up from subordinates to the manager.
• The currency here is based on currency defined when added the quota for each users.

Reference:

• Enable Quotas in Collaborative Forecasts
• Manage Quota Data for Collaborative Forecasts via Data Loader or the API

Salesforce: Get Folder Sharing Access

Report and dashboard accessibility are defined in the folder that stores the reports or dashboard, there are 3 levels of access:
1. Manage
2. Edit
3. View

You can share the folder by:
1. Users
2. Roles
3. Roles and Subordinates
4. Public Group
5. Territory (only when Territory is enabled)
6. Territory and Subordinates (only when Territory is enabled)
7. Partner Users (only when Partner community is enabled)
8. Partner Roles (only when Partner community is enabled)
8. Partner Roles and Subordinates (only when Partner community is enabled)
9. Customer Users (only when Customer community is enabled)
10. etc

Here a sample of report folder shared with users, roles, and etc.

If you see from the above screenshot, Group of GM and Staff 2 are from another screenshot, because of sharing window only able to show 5 lines at a time, so if you need to extract the list of users, roles, and etc., you need to get it from metadata.

Workbench
We can use REST Explorer from Workbench to retrieve this info. Let us walk through step by step:
1. Login to Workbench
2. Select REST Explorer under utilities menu
3. By default, the service would be /services/data/v45.0, click Execute
4. Click on folders: /services/data/v45.0/folders, click Execute
5. Click Expand All link, and search for the folder name, if you can't find it, click nextPageUrl link
6. Or, instead of looking for the report folder, get the folder Id and execute it, e.g.  /services/data/v45.0/folders/00l90000001MUVKAA4
7. Click sharesUrl: /services/data/v45.0/folders/00l90000001MUVKAA4/shares
8. Click Expand All link, now you get the list, but to put it into nice table file would need another effort

Convert JSON to CSV
8. Click "Show Raw Response" link
9. Copy the JSON file to http://www.convertcsv.com/json-to-csv.htm
10. Download the result into CSV file

Reference:

• FolderShare

Einstein Analytics: Embed Analytics Dashboards with multiple fields filter

Here is the scenario:
Show all Opportunities with filter: compare Parent_Account_ID__c value from Opportunity page (a formula field CASESAFEID(Account.ParentId)) with 2 fields from a dataset: AccountId and Parent_Account_Id__c.

As per Summer '19 release, we can use Filter Builder or Filter String, but unfortunately, the filters do not support OR logic.

Here is the workaround for the scenario:
1. Create a field in Dataset using ComputeExpression to concatenate AccountId + Parent_Account_ID__c, let us say AccountIdnParentId.

2. Use the new field created from ComputeExpression with "Contains" operator.

In case if Parent Account Id is blank, this will cause the dashboard error, you can create a custom formula field in the Opportunity to check and make sure if Parent Account Id is blank, use Account Id.

Reference: Embed Analytics Dashboards in Lightning Pages

Einstein Analytics: Gauge chart

In Einstein Analytics Gauge chart, there is only 1 value to set, which is for the needle, you can set the value from a query in step. While the value of the breakpoint are static by default, you can set Min and Max value from the user interface, but if you want to set other the breakpoint values (medium is 1/3 and high is 2/3 by default), you can set them from dashboard JSON, then also set applyConditionalFormatting to false from dashboard JSON.

But can we make the breakpoints and max numbers to be dynamic? Yes, we can use binding to make those numbers dynamic. There are 4 points can be configured:

• min: usually this is 0
• medium: usually this is X% of max
• high: usually this is Y% of max
• max: usually this is the target

Step-1. Prepare Breakpoints

Use a compare table to prepare the medium, high, and max numbers. For my use case, I'll query to get the max number from a dataset, then calculate medium = 60% of max, and high = 90% of max as breakpoints.

Clip the lens (compare table) to the dashboard, let's name it "breakpoints", then drag it to the dashboard temporarily, we will display the numbers temporary to make sure the numbers are correct. Note down the query name created, this will be used for "Step-3" below.

Step-2. Add Gauge Chart

Drag a gauge chart, select the dataset and value for the needle, and add filters (if necessary).

Step-3. Bind Breakpoints defined into Gauge chart
Note down the Widget Id (not Query Id), switch to dashboard JSON mode, looks for max, high and medium parameters.

Update the value with result binding, use the step created in "Step-1".

"medium": "{{cell(breakpoints.result,0,\"Medium\").asString()}}"

"high": "{{cell(breakpoints.result,0,\"High\").asString()}}"

"max": "{{cell(breakpoints.result,0,\"Max\").asString()}}"

* Medium, High, Max in the column name in "Step-1".

Step-4. Remove Conditional Formatting

Still in dashboard JSON, at the chart parameter, look for applyConditionalFormatting, and change this to false, by default this is true.

Step-5. Done

Here is the result

Reference: How to Make the Gauge Chart Dynamic Again

Einstein Analytics: Using XMD (Extended Metadata) for Percentage

Getting Started
When we configure Einstein Analytics Dataset, we not realize have been playing around with XMD (Extended Metadata).

When you explore dataset to a lens, and click "Fields" for:
- Rename
- Edit Values
- Number Format
Save the changes will update Dataset XMD.

Let us load a CSV file

Here is the blank structure of XMD file:
{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [],
  "dimensions": [],
  "measures": [],
  "organizations": [],
  "showDetailsDefaultFields": []
}


Let us create a dashboard based on that CSV file

Now, let us do "Fields" edit from lens with Rename, Edit Values, and Number Format. Here is the updated XMD file
{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [],
  "dimensions": [
    {
      "conditionalFormatting": {},
      "customActions": [],
      "field": "Column1",
      "label": "Name",
      "members": [
        {
          "label": "John",
          "member": "Lee"
        }
      ],
      "recordDisplayFields": [],
      "salesforceActions": []
    }
  ],
  "measures": [
    {
      "conditionalFormatting": {},
      "field": "Data_1",
      "format": {
        "customFormat": "[\"0.00%\",100]"
      }
    },
    {
      "conditionalFormatting": {},
      "field": "Data_2",
      "format": {
        "customFormat": "[\"#,###\",1]"
      }
    }
  ],
  "organizations": [],
  "showDetailsDefaultFields": []
}

Highlight notes:
- Yellow = rename
- Green = edit value
- Aqua = number format as Percent
- Fuchsia = number format as Number


Now, open back the dashboard (you need to close it first if the dashboard still open).

Usage in SAQL field
Let us do a simple SAQL to the widget to produce total Data-1 + Data-2 and Data-1 / Data-2.

q = load "percent_data";
q = group q by 'Column1';
q = foreach q generate 'Column1' as 'Column1', sum('Data_1') as 'sum_Data_1', sum('Data_2') as 'sum_Data_2';
q = foreach q generate 'Column1' as 'Column1', 'sum_Data_1' + 'sum_Data_2' as 'Total', 'sum_Data_1' / 'sum_Data_2' as 'Result_1';
q = order q by 'Column1' asc;

* notice that we still use Column1 as this is referred to the field API name

The SAQL produce two new fields which do not exist in the dataset: Total and Result_1.

However, we need to show them in the percentage format, so we need to add in the XMD file, because this is a measure field and it does not exist in the dataset, we should add it into derivedMeasures.

{
  "dataset": {},
  "dates": [],
  "derivedDimensions": [],
  "derivedMeasures": [
    {
      "conditionalFormatting": {},
      "field": "Total",
      "format": {
        "customFormat": "[\"#.##%\",1]"
      },
      "label": "Total",
      "showInExplorer": false
    },
    {
      "conditionalFormatting": {},
      "field": "Result_1",
      "format": {
        "customFormat": "[\"#.##%\",1]"
      },
      "label": "Result",
      "showInExplorer": true
    }  
  ],
  "dimensions": [
    {
      "conditionalFormatting": {},
      "customActions": [],
      "field": "Column1",
      "label": "Name",
      "members": [
        {
          "label": "John",
          "member": "Lee"
        }
      ],
      "recordDisplayFields": [],
      "salesforceActions": []
    }
  ],
  "measures": [
    {
      "conditionalFormatting": {},
      "field": "Data_1",
      "format": {
        "customFormat": "[\"0.00%\",100]"
      }
    },
    {
      "conditionalFormatting": {},
      "field": "Data_2",
      "format": {
        "customFormat": "[\"#,###\",1]"
      }
    }
  ],
  "organizations": [],
  "showDetailsDefaultFields": []
}

Notes:
1. add those field under derivedMeasures
2. field name can be different with label
3. you can set the field visibility in explorer


Here is the end result:

When you browse the dataset into a lens and open the Fields

JSON Online Editor
When you download XMD JSON file from Einstein Analytics and open in Notepad++, it would be show as one row only, where it is impossible for you to read.edit it. You can use JSON Online Editor to show as tree format, by copy and paste the JSON text into the left text area, click the right arrow to show as tree format.

Then to get human eye friendly JSON format, click left arrow and the text at the right will reformat.

Reference:

• Extended Metadata (XMD) Overview
• Tool: JSON Online Editor