Using Calculated Fields in Rules (Horizon Experience)
Overview
Calculated Fields enable two types of calculations, Aggregation and Comparison over a period of time. This function is available while configuring formula fields in the Data Transform phase of the Rules Engine.
Add a Calculated field in Rules Engine to perform the following actions:
- Create a new field that calculates a rolling average of a usage measure over the past seven days.
- Compare aggregated usage measures over the last seven days with the prior 30 days.
Add a field to compare aggregated usage measures over the last seven days with either the prior 30 days or the last two weeks versus the average of the last three months.
- Identify trends and create a Call to Action (CTA) when usage is trending low or assign a score based on whether the trend it is increasing or decreasing.
- Load data into the usage field or a custom field.
Add a Calculated Field
To add a Calculated Field:
- Navigate to Administration > Rules Engine, and create a dataset within a rule.
- Apply Transform on a dataset. A new window named Transform appears.
- On the Fields tab, click Add Formula Field. The Formula Field Builder slide-out panel appears.
-
Perform the following actions:
- In the Label field, enter a unique name for the formula.
- From the Data Type drop-down menu, select Number.
- From the Expression Type drop-down menu, select Calculated Field.
- In the Decimal Places field, enter the decimal places to round the metric's value to be measured.
- From the Time Identifier dropdown menu, select the date field for the comparison.
- From the Type of Calculator dropdown menu, select either of the following calculation types:
- Comparison: Calculates a comparison between two measures or numeric fields. For more information refer to the Comparison Type section.
- Aggregation (over time): Aggregate a measure over a period of time. (For example, count of cases in the last month). For more information refer to the Aggregation Type (over time).
Comparison Type
The Comparison Calculation Type allows you to compute the difference between two entities or aggregated entities. Choose either of the following methods to calculate the difference and define measures A and B in the Calculated fields.
- Percentage: ((A-B) / B) *100
- Actual Value: A - B
Define A
In Define A configuration:
-
Select one of the following options for Aggregation (over time):
-
In the Operation drop-down, select an aggregation type (Sum, Average, Count, Count Distinct, Min, or Max), and choose the measure to apply the aggregation to.
-
In the Timeframe drop-down, select the date range for the calculation (for example, past 2 days, past 4 weeks, or past 3 months).
-
-
Select a field.
a. From the dropdown, select a fixed metric value added in the field.
Note: Select Adjust for missing data for the aggregation type Average to skip counting the time period if the data is missing for day/week/month. For example: if you calculate the average over the past 4 weeks and data is missing for one week, that week is excluded from the average.
Define B
In the Define B configuration, select one of the following
- Select one of the following for Aggregation (over time)
- From the Operation dropdown, select an aggregation type (Sum, Average, Count, Count distinct, Min, or Max) and choose the measure to apply the aggregation to.
- From the Timeframe dropdown, select one of the following options:
-
Past: Completed period in the past.
-
Prior: Period that immediately precedes the past days/weeks/months selected in A.
-
Same Period: Same period as in N days/weeks/months as selected in configuration A.
-
-
Select a field:
a. From the dropdown, select a fixed metric value added in the field.
Note: Select Adjust for missing data for the aggregation type Average to skip counting the time period if the data is missing for day/week/month. For example: if you calculate the average for the past four weeks and data is missing for one week, that week is excluded from the calculation.
Note: If another calculated field was identified in this rule, it is available as an option here.
Time Identifier
By default, the Created Date is selected as the Time Identifier. If you use usage data in the calculated field, you may need to change the Time Identifier:
- If the Usage Configuration is Weekly, use Week Label as the time identifier. Multiple usage data records may have different dates within the same week, but the week label remains consistent for all records. This ensures there are no issues in calculations.
- If Usage Configuration is Monthly, use the Date field as the time identifier.
Aggregation Type (over time)
In the Aggregation type, calculate the aggregation of a metric over a time period.
- From the Operation dropdown menu, select an aggregation type (Sum, Average, Count, Count distinct, Min, or Max) and choose the measure to apply the aggregation to.
- From the Timeframe dropdown menu, select the date range for the calculation. For example: the past two days, past four weeks, or past three months.
- Select Adjust for missing data for the aggregation type Average to skip counting the time period if the data is missing for day/week/month. For example: if you calculate the average for the past four weeks and data is missing for one week, that week is excluded from the calculation.
Time Identifier
When you define an aggregation over time, select the date field on which the aggregation is based. Every object has a created date and a modified date. By default, one of these date fields is selected. You can view this at the bottom of the calculated metric form.
Only one date field can be picked for all the calculated metrics in the rule. There is no option to select a different date field for separately calculated metrics.
To change the time identifier (Date field), use the option at the bottom of the Calculated Field dialog for each calculated field.
Note:
- By default, one date field is selected, ensure that you review and select the appropriate one.
- Aggregation over time is possible only with snapshot data, where one record exists for each time period. If time-based snapshots are not stored for an object, you cannot calculate trends. For example, it is not correct to calculate the average of Accounts. Employees over the past month because the Account does not store time snapshots.
Min/Max Function
Selecting a Min/Max aggregation performs a function on that field to find the minimum or maximum value across all records, not a specific record. When you use Min/Max operations on a date field in a SOQL query, other fields are automatically adjusted to the opposite operation.
-
For example, if you select the maximum value of a date field, the output may display:
-
Max of Date, Min of Field 1, Min of Field 2, Min of Field 3.
-
When you build a rule on Salesforce data, remember that the Rules Engine uses a SOQL query, not a SQL query:
- SOQL supports the ability to use Min/Max on Date/Time fields.
- When Max is applied to a date field in a SOQL query, all other fields are added to Group By. If any fields are not groupable, Min is applied to them.
Limitations
-
You can add only four aggregations (over time) in a single rule.
-
You can select a max of 31 days, 52 weeks, 12 months, or two years.
-
When adjusting for missing data, you can add only four periods (days/weeks/months/years).
-
Calculated fields only work with Account-level data in Gainsight's usage data object. They will not calculate correctly if the data has any other level of granularity.
-
Date fields do not support Min/Max aggregation. If you apply Max to a date field, the system will attempt to apply Min to all other fields, and vice versa.
All of the above restrictions are due to intensive calculation that occurs in the Rules Engine system. If you need more aggregations over time, it is recommended to create a new rule to accommodate the new aggregations.