Using Calculated Fields
Calculated Fields in Spiro give users the ability to write simple expressions that can perform calculations using data in Spiro -- similar to functions in Excel. Calculated fields have built-in functions, including: if, not, min, max, sum, and round.
BUILT-IN OPERATORS AND FUNCTIONS
Math: +, -, *, /, %, ^, |, &
Also, all functions from Ruby's Math module, including SIN, COS, TAN, etc.
Comparison: <, >, <=, >=, <>, !=, =,
Logic: IF, AND, OR, NOT, SWITCH
Numeric: MIN, MAX, SUM, AVG, COUNT, ROUND, ROUNDDOWN, ROUNDUP
String: LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE, CONCAT, CONTAINS
DATE_DIFF(created_at,DATE_NOW()) [to get the exact number of days of opportunity since it was created]
CURRENCY(amount,'$') - it will return the defined amount of the opportunity and currency
DELIMINATED(amount) - should render just formatted number like 1,000,000.00 (edited)
The power of calculated fields is that you can merge fields from the record you are building them on, so that they can change dynamically. Merge fields can be any field that exists in the record and will be listed for your reference when building the calculated field.
Merge field examples:
You can also use custom fields as merge fields.
When creating calculated fields on the contact level, you may use the following out of the box merge fields from the company level in your formula:
Currently company level custom fields are not available when writing contact level formulas.
If you write a formula that causes argument error, the calculated field will display “Invalid Expression”. Meaning, you have to go back into your field and write a valid expression that Spiro can understand.
EXAMPLES AND COMMON USE CASES
Common Use Case - Annual Revenue
Let's say you work at a subscription company. You may decide to have the opportunity amount field reflect the monthly revenue from an opportunity, but you also want to view the annual revenue as well. You would then create a calculated field called "Annual Revenue", and put the following in the calculation:
amount * 12
To make that value have a currency format, you could use this instead:
Common Use Case - Weighted Pipeline
There are scenarios when you may want to reflect a separate amount value for an opportunity that changes based on the sales stage you are currently in. Create a new calculated field and call it "Weighted Pipeline" and enter the following in the calculation:
CASE opportunity_stage WHEN 'Prospecting' THEN amount *.1 WHEN 'Qualifying' THEN amount * .25 WHEN 'Proposing' THEN amount * .5 WHEN 'Closed Won' THEN amount ELSE 0 END
What this calculated field is doing is giving the Weighted Pipeline less value based on the earlier sales stage. You may sub out your own sales stages and your own weighted calculations based on your own sales process.