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.
Supported Formula Types
- Admin Page Views + Config page views
- Current Score - Previous Score
- NPS Avg * 10
- ((Current Score - Previous Score) / Previous Score) * 100
- First Name + Last Name
- CTA Comments + Custom Text
- Convert to Upper Case (Account ID)
- (Last Modified Date - Created Date) in days
- Convert to Date time (Created Date)
Period Over Period Comparison
- Moving average of a metric over a time window
- Period Over Period Comparison in % (time window, Average out past N windows)
Refer to Bionic Rules Period Over Period Comparison for more information.
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
- Period Over Period Comparison or Period Over Period Comparison in % formula cannot be created unless a date identifier is present.
- Negative values are not allowed in the Function for Period Over Period Comparison and Period Over Period Comparison in %.
- ((A-B) * (A+B)) can not be simplified to (A-B) * (A+B).
- Period Over Period Comparison and Period Over Period Comparison in %accept only integers, not decimal values.
- Concat function allows only 10 arguments.
- A maximum of only 10 formulas can be added.
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 dialog box is displayed.
Provide the following information:
- Output Field Label: Enter only alphanumeric and underscore characters in this field. 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: Enter a number between 0 and 9.
- Treat null as zero: Select this to replace any null field values with zero while performing calculations using the expression provided. Deselect this to use any null value as is in the calculations.
- Formula Type: Select Expression or Function as required. Refer to Expression and Function to learn more about formulas available in Formula Builder.
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 is also added in Setup Action > Action type >Field Mappings section.
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. The succeeding sections explain different types of functions available in Formula Builder.
The following functions allow only String, Picklist, Multi-picklist data types as input arguments.
- Concat: Concatenates the selected arguments. A maximum of 10 arguments can be added and one string can be used multiple times.
- To Lower Case: Converts the selected argument values to Lowercase.
- To Upper Case: Converts the selected argument values to Uppercase.
The following functions allow only Datetime data type as input arguments.
- Date: Converts Date-time argument to Date.
- DateDiff: Returns difference of the two selected dates in the selected unit.
The following functions allow only Date and Datetime data types as input arguments:
- First Day of Calendar Quarter: Returns the first day of Calendar Quarter.
- First Day of Current Month: Returns the first day of Current Month.
- First Day of Current Week: Returns the first day of Current Week.
- Last Day of Calendar Quarter: Returns the last day of Calendar Quarter.
- Last Day of Current Month: Returns the last day of Current Month.
- Last Day of Current Week: Returns the last day of Current Week.
Note: By default, Rule date is available in these functions.
Period Over Period Comparison functions
The following are the other available functions:
- Period Over Period Comparison
- Period Over Period Comparison in %
These functions cannot be created unless a date identifier is present. Arguments are consumed inside a function.
Example: Function syntax is as listed below. In this case, this function has a fixed number of arguments and each argument has its own context.
- Period Over Period Comparison ( Period([ DAY / WEEK / MONTH / YEAR ], <Duration>),
- <Metric [ field of type numeric ]>,
- <Date Identifier [ field of type date ]>,
- <Aggregation Method [ SUM,AVG,COUNT, COUNT DISTINCT, MIN, MAX ]>,
- <Num of Prior Periods>,< Treat Missing Data as Zero [ true/false ]>);
To accommodate this kind of requirement, Argument item is added. Refer to Bionic Rules Period Over Period Comparison for more information.