Add Calculated Field in Rules Engine might be used to:    

Add a new field that is a rolling average of last 7 days of a usage measure.

  • If the rolling average crosses a high or low threshold, create a CTA or set a score.
  • Load the rolling average to usage data or to a field.

Add a field that is a comparison of aggregated usage measure over last 7 days versus prior 30 days or last 2 weeks vs avg of last 3 months.

  • Use this to identify the trend and create CTA when it it trending low or set a score based on whether it is trending up or down.
  • Load to usage data or to a field.

This topic talks about the following:

  1. How to Add a Calculated Field
  2. Calculation Type: Comparison
  3. Calculation Type: Aggregation

How to Add a Calculated Field

How to Add Calculated Field

To get started you will go to Rules Engine and add a Custom Rule. From the Custom Rule > Setup Rule screen:

  1. Select a source object.
  2. Add all required fields to the Show section.
  3. Click Add Calculated Field.
  1. Enter the name of the new field in Field Name and then choose the type of calculation:
  2. In the Type of Caluclation list, select any of the following options:
    1. Comparison: Used to calculate a comparison between two measures or numeric fields.
      • Aggregated measure value over time (Example: Sum of Page views in last 2 weeks)
      • Measure or Numeric field that has already been added to show area
    2. Aggregation: Used to aggregate a measure over a period of time. (Example: Count of cases in the last month).

Calculation Type: Comparison

Calculation Type: Comparison

When the calculation type of Comparison is chosen, choose to Calculate difference in:

  • Percentage: ((A-B) / B) * 100
  • Actual Value: A-B

The next step will be to define A and B

Step 1: Define A

Step 1: Define A
  • Pick the aggregation of: Sum, Average, Count, Count distinct, Min or Max and the measure on which the aggregation should be applied.
  • Pick the date range in which  this calculation needs to be executed. Example: past 2 days or past 4 weeks or past 3 months.
  • Only in case of Average: There will be an option to Adjust for missing data. This needs to be selected to skip counting the time period if the data is missing for that day/week/month. Example: If you choose to do an average of past 4 weeks and there is no data for one week

Step 2: Define B

Step 2: Define B
  1. Pick the aggregation  from these options Sum, Average, Count, Count distinct, Min or Max and the measure on which the aggregation should be applied.
  2. Pick from three 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 A.

Alternatively, pick one of the Show fields in condition A, B OR Both.

Note: If another calculated field was identified in this rule, it will be available as an option.

Time Identifier

Time Identifier

By default, Time Identifier will be Created Date. This can be changed within the Setup Rule page once the field has been saved by clicking OK. If you are using Usage Data in the calculated field, you will most likely want to change the Time Identifier:

  • If Usage Configuration is Weekly, use Week Label as time identifier. There is a chance that the same week has Usage Data records with different dates, but week label will be the same for all of the records in a week, so you will not see any problems in calculations.
  • If Usage Configuration is Monthly, then Date field is the best option for the time identifier.

Calculation Type: Aggregation (over time)

Calculation Type: Aggregation (over time)
  1. Pick the aggregation from options of: Sum, Average, Count, Count distinct, Min or Max
  2. Pick the measure on which you want to apply the aggregation.
  3. Pick the date range in which this calculation needs to be executed. Example: past 2 days or past 4 weeks or past 3  months.
  4. Only in case of Average, the option to Adjust for missing data is available. This needs to selected to skip counting the time period if the data is missing for that day/week/month. Example: If you choose to do an average of past 4 weeks and there is no data for one week then the sum is divided by 3 instead of 4.

Time Identifier

When you define an aggregation over time, you need to pick the date field on which the aggregation should based on. Every object has created date and modified date, by default one of the date fields is picked. You can see that 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 separate calculated metrics.

To change the time identifier, look for the Time Identifier option at the bottom of the Setup Rule screen.

Notes:

  • Since by default, one date field is selected, ensure you review this and select the appropriate one.
  • Any aggregation over time can be done only when we have snapshot data, as in one record for each time period.  For the objects where you do not store time based snapshots, you  will not be able to calculate the trend.  For example, it is not correct to calculate AVG of Account.Employees in Past 1 Month because Account does not store time snapshots.

Min/Max Function

Selecting a MIN/MAX aggregation is not selecting a record, it is performing a function on that field (which is find the MIN/MAX in summary for all records). When using MIN/MAX operations on a date field in a SOQL query, it changes other fields to the opposite operation. For example, let's say you want to view the last record of the month. You use MAX of Date and you get the following:

Show: Max of Date, (MIN of) field 1, (MIN of) field 2, (MIN of) field 3.

Limitations

  1. You can add only 4 aggregations (over time) in a single rule.
  2. You can select a max of 31 days or 52 weeks or 12 months or 2 years.
  3. You can add only 4 periods (days/weeks/months/years) when adjusting for missing data.
  4. Calculated fields only work with Account level data in Gainsight's usage data object. It will not calculate correctly if the data has any other level of granularity.
  5. Date fields don't support max/min aggregation. If you use max on the date field, it will try to find the min on all of the other fields. The same is true for using min (it will max out the other fields).  

All of the above restrictions are due to intensive calculation that happens in the memory of the Rules Engine system. If you need more aggregations over time, it is recommend creating a new rule to accommodate the new aggregations.