This article reviews the Formula fields supported in Bionic Rules. The +Formula Builder button can be seen only when you perform the Transformation tasks. This feature allows you to create different types of formulas so that you don’t have to create the formula on the Object directly in SFDC or MDA.
- Allows you to build a numerical expression on fields with all arithmetic operators.
- Enables you to generate a formula on the columns of a single record.
- Enables you to generate a complex formula on multiple record sets.
- Enables you to perform complex functions such as SUM, AVG, or AGGREGATE of a column in a set of rows.
Types of Formulas
Following are the types of formulas used in Formula Builder in Bionic Rules:
- Admin Page Views + Config page views
- Current Score - Previous Score
- NPS Avg * 10
- ((Current Score - Previous Score) / Previous Score) * 100
Trend Formulas in Rules Engine
- Moving average of a metric over a time window
- Trend in percentage (time window, Average out past N windows)
Sample Expression Syntax
<FIELD/VALUE> <OPERATOR> <FIELD/VALUE>
- Expression cannot be added when GroupBy field is added to task.
- Constants in Formula Field are not considered as token.
- Formula time span cannot exceed 2 years
- Trend or Trend in Percentage formula cannot be created unless a date identifier is present.
- Negative values are not allowed in the Function for Trend and Trend in Percentage.
- ((A-B) * (A+B)) can not be simplified to (A-B) * (A+B) in this release.
- Trend and Trend in Percentage accept only integers, not decimal values.
Add Formula Field
Navigate to Administration > Rules Engine > +RULE (Bionic - Formula Builder Test).
Click NEXT to navigate to Setup Rule screen.
In Setup Rule screen, click the edit icon for the aggregated task (Transformation Task) as shown in the following image.
Note: You can create a Formula Builder only for Transformation Task.
Select the Source Object (here, Formula Builder Test) and add the required fields in the Show section.
Click +Formula Builder available on the Setup Rule screen. The Formula Field page opens (refer the following image).
Provide the required information in the following list of fields available in the Formula Field page (refer the image below):
- Output Field Label: This name contains only alphanumeric and underscore characters. The label should always start with an alphabetical character.
- Output Header: This name is used as CSV Header to process action. This is view only and is generated based on the Output Field Label (but without space).
- Decimal Places: Number should be between 0 and 9
- Formula Type: When you add a Formula Field, you have the following Formula Types available: Expression and Function
All arithmetic operators are considered as Expression. The primary items in Expression are:
- Operator: *, +, -, and /
- LHS: Left hand side Token to the operator (Currency Field or Numeric Field)
- RHS: Right hand side Token to the operator (Currency Field or Numeric Field)
If any LHS or RHS is missing in the Formula Field, the following message is displayed when you try to SAVE the formula.
Arguments: The list of arguments(Tokens) participating in the method for evaluation.
Note: If tokens are not entered, the following message is displayed.
Arguments length: Max number of arguments that this method can accept. Especially in case of multi row scenario
Some calculations were already available in Engagement Rules, but since they are deprecated in the 5.6 release, Gainsight now offers the Formula Builder in Bionic Rules
In the Formula Field window, when you select Function as Formula Type, the following message appears on the screen.
Trend or Trend in Percentage formula cannot be created unless a date identifier is present. Arguments are consumed inside a function.
Example: Trend function syntax is as below. In this case, this function has a fixed number of arguments and each argument has its own context.
- Trend( Period([ DAY / WEEK / MONTH / YEAR ], <Period Length>),
- <Source Field [ field of type numeric ]>,
- <Trend Indicator [ field of type date ]>,
- <Aggregation Method [ SUM,AVG,COUNT, COUNT DISTINCT, MIN, MAX ]>,
- <Prior Period>,< Adjust Missing Data [ true/false ]>);
To accommodate this kind of requirements, Argument item is added.
Click SAVE. Clicking RESET erases the data that you entered and allows you to refill the field with fresh information.
In Setup Rule, you can see the newly created Formula Field is added in the Show section as shown in the following mage.
The same field will also be added in Setup Action > Action type >Field Mappings section.
The following are the defined java based patterns to validate the supported formulas:
|A/B||case folders_created when 0 then 0 else days_active/folders_created end||Pass|
|A+(B/C)||case link_clicks when 0 then 0 else days_active+(folders_created/link_clicks) end||Pass|
|(A+B)/C||case link_clicks when 0 then 0 else (days_active+folders_created)/link_clicks end||Pass|
|(A-B)/C||case link_clicks when 0 then 0 else (days_active-folders_created)/link_clicks end||Pass|
|A*(B/C)||case link_clicks when 0 then 0 else days_active*(folders-created/link_clicks) end||Pass|
|A*B||case (days_active*folders_created) when 0 then 1 else days_active*folders_created end||Pass|
|A*B||case (days_active*folders_created) when 0 then 1 else null end|
|((A/B) + (C/B))||case folders_created when 0 then 0 else (days_active/folders_created)+(link_clicks/folders_created) end||Pass|
|((A/B) + (C/B))||case folders_created when 0 then Null else (days_active/folders_created)+(link_clicks/folders_created) end||Pass|
|A||CASE WHEN A BETWEEN 1 AND 50 Then 3 When A Between 51 And 100 Then 5 When A > 100 Then 10 Else 0 End||Not Supported|