In QuotaPath, Admins can create custom calculated fields using fields from integration sources to enable custom logic beyond what is available in the mapping manager. Custom calculated fields can be used in mappings and as additional fields, surfacing additional data on Earnings, Attainment, Deals, Approvals and Payouts pages.
Calculated fields are most often used to enable logic more complicated than the mapping modal allows. Because the mapping model automatically inserts AND/OR between criteria fields and does not allow any customization of the AND/OR logic, sometimes calculated fields are necessary when the logic needs to include something more custom or complex.
Calculated Fields
Calculated Fields in QuotaPath can only be created using the source data from a single object. If you select Opportunity as your source object to create a calculated field, for example, you can use any Opportunity source data field in your logic. You cannot, however, reference line item or account fields (other objects). QuotaPath can enable that kind of complex computed field, but it would have to be built by our Solutions Engineering team through a special request and may require your workspace be on the Premium tier. Talk to your AM or CSM if this is needed.
To access your Calculated Fields, click Integrations in the left hand navigation menu, click Manage on the Integration card for the data source you want to build with and then click on the Calculated fields tab.
Once on the Calculated Fields tab, select from the Object dropdown the object on which you'd like to build the calculated field and click '+ Create New Calculated Field.'
Builder
After clicking '+ Create New Calculated Field', you'll be redirected to the Builder tab. In the Add Calculated Field panel on the right-hand side, provide the following details for your calculated field:
Source: The integration source containing the field(s) you'll use to create the calculated field
Object: The object in the above source containing the field(s) you'll use to create the calculated field
Then:
Name: The name for the calculated field, which will appear throughout your QuotaPath workspace. Best practice is to use some prefix, like "Custom:" or "Calc:" so that users seeing the field in QuotaPath will be reminded that this is a calculated field and not a CRM/Data source field.
Type: The type of field - Number, Text, Boolean, or Date. Boolean means Your logic will be evaluated and return either be true or false.
Formula Description (Optional): Space to write a plain language version of your logic.
Formula container: This is where you write your query logic.
+ Add Source Fields: Click to select and add any field from the object you are building your calculated field on. You will only be able to select fields or properties from that object.
Allowed operators and full documentation: QuotaPath's calculated field features use Postgres SQL. These links will show you which operators and notations can be used.
Preview: Once you have written and saved a query, the preview button will become available, and clicking preview will test your query against your most 1,000 most recent records and show you the results.
Preview Results: This is where the results of your preview will show.
Sync and Refresh: In order to make your calculated field available to use in mappings or as an additional field, you'll need to click Sync and Refresh and let the sync complete.
Writing your Calculated Field logic
Enter the formula for the field in the formula container on the right-hand side (#4 above). Formulas must be built using Structured Query Language, or SQL. For comprehensive documentation on SQL functions and operations, please refer to the SQL documentation available here.
To use your data source fields or properties in your logic , click '+Add Source Fields' in the top right-hand corner. You'll see the below pop-up modal which will preview fields from your selected object and source. Click '+ Add' next to the field that you'd like to add into your formula. You can add multiple fields at once.
After adding the field, it will automatically appear in the formula container. From there, you can use SQL formulas and operators to build out your calculated field. For more information on the most common operators, click 'Allowed Operators' which will open the below pop-up modal.
After building your calculated field, click 'Save'.
Then, you can click 'Preview' and scroll down in order to preview the calculated field results based on the 1000 most recent deals in your workspace to confirm the accuracy of your formula.
Note: Calculated fields can only use fields on the same object in the same source. Additionally, calculations can only reference a single record and can not analyze an aggregation of records. For more advanced computations analyzing sums of records or multiple sources, please reach out to your Customer Success Manager(if you're still in onboarding), your Account Manager (if you've completed onboarding) or to our Support team via the in-app chat.
Sample Calculated Fields
To help get you started with building calculated fields, here a few examples of common calculated fields and the SQL formula for each:
Convert MRR to ARR
{ MRR } * 12Calculate Retention Rate
{ Renewed Amount } / { Renewal Amount } Convert Field Type
{ date }::date Contract Term Multiplier
CASE WHEN { contract term } <= 12 THEN { ARR } * 12 ELSE { ARR } END
Map Calculated Fields
Once the calculated fields have been created and plans have been built, you'll also need to map to this calculated field in any mapping and plan that will be using this calculated field to calculate commission, attainment or inform payout eligibility.
Once you have created the calculated field and run a successful refresh and sync, you can then go into any mapping and select your calculated field as seen below. calculated fields will show a small "(fx) Calculated Field" label on the right side of the dropdown row.
Complete the component mapping and sync the data. You'll need to complete this step for every component in every plan in which you'll be using this calculated field.
View Calculated Fields
After you've mapped your calculated fields, you can start viewing the resulting data in your QuotaPath workspace. Go to the Deal, Earnings or Attainment page to view data for specific workspace members, or head to the Deals or Payout pages to view data across multiple members.
As an admin, you can view and edit all your built calculated fields on the Calculated Fields tab on the Integrations page. You will see the Name, Type, Created By user, Formula, Description and the Usage. Hovering over the number of mappings on the Usage tab will show you the names of the mappings in which the field is used and allow you to click on the mapping name to view that mapping.
Computed Fields
In the calculated field table on the calculated fields tab in your integration details, you can see descriptions for both your computed fields and calculated fields.
Computed fields is the term that QuotaPath uses for custom fields built on the QuotaPath backend by our solutions engineering team to enable custom multi-object and/or multi-data-source logic. Single source computed fields are included with Growth tier workspaces, where multi-source computed fields are only for Premium tier workspaces. Both require QuotaPath to build the fields.
So, in summary, Calculated fields are able to be edited in QuotaPath. You can view the logic and make changes to them.
Computed fields limit you in-app to just the description of the logic. To edit these fields or test resulting values with sample records, you will need to reach out to your QuotaPath contact, or our Support team.