Salesforce: Using Custom Field for Forecasts

This blog only applicable for Collaborative Forecast, as per latest announcement, Customizable Forecast is scheduled for retirement as of Summer ’20.

By default, Salesforce will forecast using Amount field from Opportunity for Revenue forecast. However, to fit your business needs, you can add additional forecast type using custom fields in Opportunity, and the fields must be a currency field.

1. Opportunity Split
In this blog, I will add a custom currency field from Opportunity called Extra Income. I'll select the field 'Extra Income' and type in 'Extra' as Split Label.

Tips: on the Split Type, the Totals 100% must be ticked, otherwise, forecast with the custom fields will not work.


2. Forecast Setting
Now, I need to configure forecast setting, click the link "+ Add another forecast type" then select Extra.

Then, you select a Forecast Measurement (Revenue or Quantity) and select fields to show in the Opportunity List.
You need to click Save button to save, otherwise, it will not save, even you have click OK button in many screens.


Forecasts tab
Now, let us see if this will work.

This is the default forecast based on Amount, it called Opportunity Revenue.

Now, let us flip to the new Extra forecast we just create, click the gear icon on the top right.

Now, let us forecast with our new forecast type "Extra"

Reference: Enable Custom Field Forecasts in Collaborative Forecasts

Salesforce: Sort Report

Create and sort report is simple, but sometimes you will wonder why the report does not produce in the way it should be, example report below:

We sort the report by Brand -- which is a picklist field, but the order is not working properly, I expect it should sort by alphabetically, the same order result we will see in Classic.

Try to convert the report to a summary or matrix report:

summary report

matrix report

What is the cause?
The order of groupings containing Picklist field values is based on how the values are arranged in the picklist field itself, not the arrangement selected in the Sort Order.

The easy fix is by change the picklist value for that field, see this article Sort Picklists, if you need guidance on how to change picklist value.

However, if you don't have admin access, or for some reason, you are not allowed to change the order, you can add bucket field in the report, make sure to create the buckets in the order that you want the picklist values to be displayed on the report.

So, instead of using the original field, use this bucket field in the report.

Here an idea to vote to sort picklist values alphabetically, currently only have 470 points after 9 years.


Reference:

• Sort grouped data on a Report
• Summary Report Grouping Sorted by Picklist Order
• Sort Picklists

Using Emoji in Salesforce

Nowadays, emoji is widely used from mobile device messaging, email, until more serious business applications such as Salesforce. If you don't have emoji keyboard, you can copy varieties of emoji from the internet, one of the most famous is emojipedia.org

As per this article 5 Ways You Can Use Emoji, you can use emoji in Salesforce, from:
- Chatter Post
- Validation Rules
- Help text
- Formula field
- Picklist values
- Field value
- List view

Emoji in Salesforce works in both Lightning and Chatter.

Reference:

• How To add Emoji to List View Names in Salesforce ⭐️
• 5 Ways You Can Use Emoji

Einstein Analytics: Security Predicate setting

Einstein Analytics supports security predicates, a robust row-level security feature that enables you to model many different types of access controls on datasets. Einstein Analytics also supports sharing inheritance, to synchronize with sharing that’s configured in Salesforce, subject to certain limitations. If you use sharing inheritance, you must also set a security predicate to take over in situations when sharing settings can’t be honored.

This blog will discuss setting up a security predicate for a Dataset created from the CSV file. By default, when you load the CSV file to create a new dataset, the security predicate will be empty, which means everyone who has access to the dataset can see all rows.

We can build a security predicate even for CSV files that do not originally come from Salesforce, as long as there is an identifier that links between CSV files with Salesforce data. We can build a security predicate after the dataset is created in Einstein Analytics.

Syntax
<dataset column> <operator> <value>

Examples
'UserId' == "$User.Id"

• UserId is the API name of the dataset
• ' is required for the dataset column, even without a dot (.)
• == is the operator
• $User.Id this is the current Salesforce User ID when opening the dashboard or lens

If you check the above basic syntax again, then change the syntax to "$User.Id" == 'UserId', this syntax becomes invalid and will be rejected by the system. Even if the values are the same, the security predicate must always start with the dataset column and not the other way around.

You can use and && and or || logical operators in the security predicate
('Expected_Revenue' > 4000 || 'Stage Name' == "Closed Won") && 'isDeleted' != "False"

Consider the following requirements for the predicate expression:

• The expression is case-sensitive.
• The expression cannot exceed 1,000 characters.
• There must be at least one space between the dataset column and the operator, between the operator and the value, and before and after logical operators. This expression is not valid: 'Revenue'>100. It must have spaces like this: 'Revenue' > 100.
• Support string and numeric values, but not Boolean
• If you are using custom fields in the user object, make sure the Insights Security User has read permission to the custom fields

How to create exceptions?
This means that a security predicate should not impact a group of Salesforce users. One simple idea is to add unique values, such as User Role ID, User Profile ID, or a custom field from the User object, to the dataset security predicate and to the data itself.

Scenario: All users with Profile = Executive are allowed to see all data; otherwise, they can only see data the same as user Territory. In this scenario, Territory is a custom field in the user object and is also available in the Dataset.
1. Get the Profile ID of the Executive Profile
2. Add Profile ID from (1) as a column to all rows in the CSV file before loading to Einstein Analytics
3. Load the CSV file to Einstein Analytics
4. Edit the dataset created and create a security predicate as follows

'Territory' == "$User.Territory__c" || 'Executive_ProfileId' == "$User.ProfileId"

The first part (in purple) allows users to see only data where the Territory in the dataset is the same as the Territory defined in the user object.
The second part (in green) allows all users with Executive Profile to see all data; that's why we use logic (||).


Using the same method, you can add Role as an exception too, just add another column and fill the Role Id to all rows. However, if you need to define more than 1 profile or 1 role, you need to keep duplicating the columns in the CSV file and add use || as the exception, e.g., 'Territory' == "$User.Territory__c" || 'Executive_ProfileId' == "$User.ProfileId" || 'Strategy_ProfileId' == "$User.ProfileId", I know this is not a pretty solution, but it works.

You can define a dataset security predicate by editing the dataset and entering a valid Security Predicate.

The system will check and reject if the security predicate syntax is invalid, such as "$User.Id" == 'UserId' (wrong order), also if the value does not exist, such as: 'UserId' == "$User.Field__c" (Field__c field does not exist in User object). However, the system will not validate and not reject if the column name does not exist, such as: 'UserField' == "$User.Id" (UserField does not exist in the dataset column).

Note:
1. If you replace data for an existing dataset, the security dataset defined will stay, including when you restore from the previous dataset. This also includes if you change the Security Predicate value in the dataflow register node, the security predicate in the dataset regenerate will stay, so you need to manually update it in the dataset.

2. Dataset column <dataset column> <operator> <value> could be a multi-value, for example: 'AccountTeam.UserId' == "$User.Id"

AccountTeam.UserId is a Lookup Multiple Values that result from an augment node, from User to Account Team. This security predicate allows any users in the Account Team for the ability to see the dataset rows.

Logical Operators

• && for logical AND, for example: 'Stage Name' == "Closed Won" && 'isDeleted' != "False"
• || for logical OR, for example: 'Expected_Revenue' > 4000 || 'Stage Name' == "Closed Won"

Comparison Operators

• == : Equals, for example: 'Stage Name' == "Closed Won"
• != : Not equals, for example: 'isDeleted' != "False"
• < : Less than, for example: 'Revenue' < 100
• <= : Less or equal
• > : Greater than
• >= : Greater or equal

in : true if the left operand exists in the list of strings substituted for a multivalue picklist (field value), for example: 'Demog' in ["$User.Demographic__c"]

In this example, Demographic__c is of type multiPicklistField. During evaluation, the multivalue picklist field is substituted by a list of strings, with 1 string per user-selected item.

Comma-separated lists are not supported within the square-bracket construct.

Reference:

• Analytics Security Implementation Guide
• Predicate Expression Syntax for Datasets
• Comparison Operators

Einstein Analytics: Understanding Nodes in Monitor

1. CSV File Load
When we create a new dataset using CSV file, here are the items in the Monitor:

Let's understand each item and the Node Type, data flow title sample_data_4 Upload flow - Overwrite:
- sample_data_4 is the dataset name, not CSV file name;
- Upload flow - Overwrite suffix is always the same for all CSV load.

Nodes involved for CSV data load:

• sfdcFetch
• csvDigest
• optimizer
• sfdcRegister

When we replace dataset with a new CSV file, the title and nodes in Monitor will stay the same.


2A. Simple data fetch from Salesforce
Here we have a simple dataflow with 2 nodes: sfdcDigest and sfdcRegister.

Items in the Monitor:

The title is Dataflow name, and nodes involve in Monitor for this dataflow:

• sfdcDigest
• optimizer
• sfdcRegister

What happened if we add filter conditions to the sfdcDigest node? Will it change the nodes in the monitor? The answer is No, because the filter happens inside sfdcDigest node only.

optimizer is always run before sfdcRegister for each sfdcRegister mode.


2B. Adding augment nodes to Dataflow

Here are nodes in the Monitor:

From the above screenshot, we have another sfdcDigest node for getUser, and augment node.


2C. Adding sliceDataset and filter nodes to Dataflow

Here are nodes in the Monitor:

Now we have addition nodes: sliceDataset and filter as per order in the data flow.


2D. Add edgemart and computeExpression nodes to Dataflow

Here are nodes in the Monitor:

Edgemart note start first and computeExpression node is run after augmentAccount_User, so this order as per dataflow. From the screenshot edgemart and computeExpression also run sliceDataset node with name DropSharingRulesFrom-, further check, this DropSharingRulesFrom- is randomly added, it can be appeared for sfdcDigest or augment node too, I am still checking what is the caused.


3. Trend Salesforce Report
Next, let us see how Trend from Salesforce report to Einstein Analytics. When you setup Trend for the first time from Salesforce report, it will run once to create dataset and dashboard, this activity happens before the scheduled date/time.

Sample from Monitor:

There are only 3 nodes here:

• sfdcFetchReport
• optimizer
• sfdcRegister

But, when the scheduler running, this is the nodes:

Let us see each node:

• edgemart - to read existing dataset
• sfdcFetchReport - to get data from Salesforce
• let us ignore DropSharingRulesFrom
• append - to add existing dataset data read from edgemart, with new data from sfdcFetchReport
• optimize and register by overwrite the dataset

4. Recipe with Append

This is a simple recipe to add a dataset to another dataset and produce new dataset.

When we run the recipe, here are nodes in the Monitor:

Let us see each node:

• edgemart from append (new) table and edgemart from root (based) table
• let us ignore DropSharingRulesFrom
• two computeExpression nodes
• append transformation node
• slideDataset transformation node
• optimizer and sfdcRegister nodes

Einstein Analytics: Using SOQL

So far, we all know, in building dashboards in Einstein Analytics, we need to bring the data into Einstein Analytics and stored it as Dataset. In this blog, I will share how to directly get data from Salesforce using SOQL, this means we can create a chart wizard or table in  Einstein Analytics with Salesforce live data.

You need to know the basic JSON dashboard in Einstein Analytics. After you create the dashboard, create a step with the type = soql, e.g.

 "soql_step_name": {  
  "type": "soql",  
  "query": "SELECT Name from ACCOUNT",  
  "strings": ["Name"],  
  "numbers": [],  
  "groups": [],  
  "selectMode": "single"  
 }  

Once the step added, you can use it in any wizard. The isFacet and useGlobal properties don't apply to this step type. You can use a binding to filter other steps based on a selection in a soql step.

Let's see more samples:

 "soql1": {  
         "type": "soql",  
         "query": "SELECT Id,Name,NumberOfEmployees,Type from ACCOUNT",  
         "strings": [  
           "Type",  
           "Id",  
           "Name"  
         ],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "groups": [],  
         "selectMode": "single"  
       }  

 "soql2": {  
         "groups": [],  
         "numbers": [  
           "foo"  
         ],  
         "query": "SELECT count(id) foo from ACCOUNT",  
         "selectMode": "single",  
         "strings": [],  
         "type": "soql"  
       }  

 "soql3": {  
         "groups": [],  
         "numbers": [],  
         "query": "SELECT Id,Name from USER where Id = '{!User.Id}'" ,  
         "selectMode": "single",  
         "strings": ["Id","Name"],  
         "type": "soql"  
       }  

  "soql4": {  
         "type": "soql",  
         "query": "SELECT NumberOfEmployees,Name,Type from ACCOUNT",  
         "strings": [  
           "Type",  
           "Name"  
         ],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "groups": [  
           "Type"  
         ],  
         "selectMode": "single"  
       }  

Notes:
- same as the normal step in JSON, the order of parameters will be ignored
- type parameter is "soql"
- query parameter must be valid soql and contain all fields needed
- fields from query result should be put under strings or numbers parameter
- groups parameter is optional, but needed when you have grouping in the wizard


Here is the wizard result from each step above:

step soql1

step soql2

step soql4

Use SOQL result for binding

 "soql1": {  
         "groups": [],  
         "numbers": [  
           "NumberOfEmployees"  
         ],  
         "query": "SELECT Id,Name,NumberOfEmployees,Type from ACCOUNT order by NumberOfEmployees",  
         "selectMode": "single",  
         "strings": [  
           "Type",  
           "Id",  
           "Name"  
         ],  
         "type": "soql"  
       }  

 "all_1": {  
         "type": "saql",  
         "query": "q = load \"All_Accounts\";\nq = group q by all;\nq = foreach q generate {{cell(soql1.result,1,\"NumberOfEmployees\").asObject()}} as 'name1';\n",  
         "useGlobal": true,  
         "numbers": [],  
         "groups": [],  
         "strings": [],  
         "visualizationParameters": {...},  
         "label": "all_1",  
         "selectMode": "single",  
         "broadcastFacet": true,  
         "receiveFacet": true  
       },  

result:

* 18 is the result from the second row cell(soql1.result,1,\"NumberOfEmployees\").asObject()



Reference:

• soql Step Type Properties
• SOQL Step in Salesforce Analytics Cloud Wave