Calculated Insight DATE_ADD function errors when attempting to reference a Number field instead of a static integer value
Trailblazer Community

Calculated Insight DATE_ADD function errors when attempting to reference a Number field instead of a static integer value

CDP

Last updated 2022-02-22 ·Reference W-10731795 ·Reported By 0 users

In Review

Summary
The following Help document outlines the option of using the DATE_ADD function to add a time or date interval value to a date value:
https://help.salesforce.com/s/articleView?id=sf.c360_a_calculated_insights_date_time_operators.htm&type=5

When creating a Calculated Insight and configuring your SQL statement within Salesforce CDP you are able to reference a Number field from a data source as the second parameter of the DATE_ADD function. However, SQL statements configured this way will error. Instead you need to reference a static integer as the value (e.g. 5, 10 etc).

Repro
- In Salesforce CPD, navigate to Calculated Insights tab.
- New > Create with SQL.
- Enter an SQL statement that includes a DATE_ADD function that references a Number property as the second parameter -
e.g. DATE_ADD( TEST__cio.MAXDATE__c , TEST_cio.TEST_DAY__c )

Note: Calculated Insight with have Last Run Status: Error

Workaround
Instead of referencing a Number field, reference a static value -
e.g. DATE_ADD( TEST__cio.MAXDATE__c , 10)

Reported By (0)

Any unreleased services, features, statuses, or dates referenced in this or other public statements are not currently available and may not be delivered on time or at all. Customers who purchase our services should make their purchase decisions based upon features that are currently available.