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.
Additionally, these issues can also occur when handling subscription-related data, such as scenarios where the "Current Subscription End Date" field is null or empty.
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.
Null Dates in Subscription Data: If subscription-related fields like "Current Subscription End Date" are not populated, calculations relying on these dates will fail unless handled properly in the formula.
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)
Alternatively, you can configure your formula field to apply filters that remove null dates, ensuring accurate calculations using available data. For instance, when working with subscription traits, filter to pull the most recent valid date.
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
Leverage Formula Traits with Custom Objects: Utilize formula traits to aggregate and calculate data linked to custom object instances for comprehensive account-level insights.
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.
Roll Up Specific Data: Formula traits can extract and summarize data from specific custom object instances, such as rolling up the name of a contact from an "Advocacy" instance to account level.
FAQ
How do I handle null dates in subscription traits? Configure filters in your formula field to use the most recent date where a value is set, skipping null dates for accurate calculations. Can formula traits aggregate data from custom objects? Yes, formula traits can sum or roll up data across all custom object instances or retrieve specific data to provide versatile account-level customization.
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.