Problem Description
When creating formula traits in Vitally to calculate values (e.g., ARR for a financial year), null values are returned when no data matches the criteria. This causes calculations (e.g., differences between years) to fail, as they cannot handle null values.
Potential Reasons for the Issue
Null Values in Data:
If the fields referenced in the formula contain null values, the formula trait will return null instead of 0.
Formula Logic:
Formula traits do not automatically replace null values with 0 unless explicitly defined in the formula logic.
Step-by-Step Solutions
1. Use the Coalesce Function
The
Coalescefunction replaces null values with a specified default value (e.g., 0).Update your formula as follows:
Add the
Coalesceoperator to the formula.Wrap the existing formula inside the
Coalescefunction.Specify 0 as the default value.
Example:
coalesce(<Your Existing Formula>, 0)
For example, if your original formula was:
Sum Opportunities (Total Deal Value USD - discount)
Update it to:
coalesce(Sum Opportunities (Total Deal Value USD - discount), 0)
2. Test the Updated Formula
Save the updated formula and allow up to an hour for the recalculation to complete.
Check the affected accounts to confirm null values are now replaced with
0.
3. Verify the Results
Navigate to an example account with previously null values (e.g., Westfalia Chile Account).
Confirm the formula trait now displays
0instead of null for accounts with no matching data.
Additional Tips
Audit Data Regularly: Ensure the fields referenced in your formulas are populated with accurate and complete data to minimize null occurrences.
Raise Feedback for Enhancements: If more robust null-handling capabilities are needed, consider submitting product feedback to Vitally.
FAQ
Why are null values causing my calculations to fail?
Null values indicate missing data. Formula traits cannot calculate results when a required value is null unless explicitly handled in the formula logic.
What does the Coalesce function do?
The Coalesce function replaces null values in a formula with a specified default value (e.g., 0). This ensures calculations can proceed without errors.
How long does it take for updates to reflect?
Formula traits typically take up to an hour to recalculate. If changes don’t appear after this period, contact Vitally Support for assistance.
Can I use other functions like If/Else instead of Coalesce?
While If/Else can be used for conditional logic, Coalesce is specifically designed for replacing null values and is more straightforward for this use case.