CRM Analytics: Recipe formula syntax in Transform node

For those who have used Dataflow in the past and migrated to Recipe recently, some of the syntax and functions are different from SAQL in Dataflow.

1. string
use ' instead of "

in SAQL, case when 'Data.Name' is null then "No" else "Yes" end

in the recipe, case when "Data.Name" is null then 'No' else 'Yes' end

2. operand in comparison

in the recipe, equal, use = instead of ==

unequal, use !=

3. operand logic

in the recipe, use AND or OR

4.  instr() to replace index_of() 
Syntax: instr(field, searchString, [position, [occurrence]])
instr('123!456!78!', !) -- the result is 4
instr('123!456!8!', !, [4])  -- the result is 7
instr('123!456!8!', !, [4, [2]])   -- the result is 9
case when instr(Email, '@ap') > 0 then 'APAC' when instr(Email, '@eu') > 0 then 'EMEA' else 'AMER' end

5. to_timestamp() to replace toDate()

in SAQL, case when 'Region' == "APAC" then toDate("2024-01-06 21:00:00") 

else toDate("2024-01-07 11:00:00") end

in the recipe, case when "Region" = 'APAC' then to_timestamp('2024-01-06 21:00:00') else to_timestamp('2024-01-07 11:00:00') end

The output type from to_timestamp is Date Time.

6. Formula in Compute Relative

Make sure the "Multiple row formula" is selected

in SAQL, case when previous(CaseId) is null  then "Yes" else "No" end

in the recipe, case when lag(CaseId) IS NULL then 'Yes' else 'No' end

7. format_number() to replace number_to_string()

in SAQL, case when 'OwnerHistory.OwnerChangeCount' is null then "00" else number_to_string('OwnerHistory.OwnerChangeCount',"00") end

in the recipe, case when "OwnerHistory.OwnerChangeCount" IS NULL then '00' else format_number("OwnerHistory.OwnerChangeCount", '00') end

8. Date/time comparison

in SAQL, case when ('CreatedDate_sec_epoch' >= StartDateTime_sec_epoch) && ('CreatedDate_sec_epoch' <= EndDateTime_sec_epoch) then "Yes" else "No" end

in the recipe, case when (to_unix_timestamp(CreatedDate) >= to_unix_timestamp(StartDateTime)) AND (to_unix_timestamp(CreatedDate) <= to_unix_timestamp(EndDateTime)) then 'Yes' else 'No' end

9. substr()

Formula substr() has no change in recipe

case when substr(OwnerId, 1, 3) = '00G' then 'Yes' else 'No' end

10. IN() function

in SAQL, Type in ["Transfer", "TransferredToSbrSkill"]

in the recipe, Type IN ('Transfer', 'TransferredToSbrSkill')

 

Reference:

• String Functions for Formulas
•  SAQL samples in computeExpression

Bucket field in CRM Analytic

There are a few options to bucket field in CRM Analytics; let us see each option:

Scenario: field name = Option, bucket value A, B, C as "Executive", D, E, F as "Premier", all others as "Standard".

1. SAQL in the dashboard

Edit the widget and hit query mode

q = load "SalesData1";
q = filter q by 'Country' == "Singapore";
q = filter q by 'Option' is not null;
q = foreach q generate (case when Option in ["A","B","C"] then "Executive" when Option in ["D","E","F"] then "Premier" else "Standard" end) as 'Bucket';
q1 = group q by 'Bucket';
q1 = foreach q1 generate 'Bucket', count(q1) as 'Count';

This will work; however, there will be issues:

• Unable to broadcast selection as facet from the chart with SAQL because the identifier is unknown. When selecting the grouping, other widgets will get an error "Can't display this widget. This widget can't be displayed because there is a problem with its source query: Undefined identifier: "SAQL Field As". Make sure the "SAQL Field As" identifier exists and is spelled correctly without spaces.". However, the widget will still be able to receive filters from other widgets. You can deactivate "Broadcast selections as facets" for that query, but it is not ideal.
• Performance factors, system recommended: No Groupings After Projections and No Case Statements in Projections

Another sample:

q = load "SalesData1";
q = filter q by 'Country' == "Singapore";
q = filter q by 'Option' is not null;
q = group q by ('StartTime', 'Owner.Region__c');
q = foreach q generate q.'StartTime' as 'StartTime', (case when 'Owner.Region__c' is null then "Unknown" else 'Owner.Region__c' end) as 'Region', count(q) as 'Count';
q = order q by ('StartTime' asc, 'Region' asc);
q = limit q 2000;

2. Edit field value

Issue:

• Loss of the original field value
• The fields will not be grouped, even if they have the same value

From the screenshot above, the three Executive originally were A, B, and C; they are not combined by the system.

3. Using Recipe or Dataflow to create new fields

This is the most ideal option, but you have to edit the recipe or data.

CRM Analytics: SAQL samples

computeExpression is one of the most powerful features in Dataflow in computeExpression, you can "add" fields without having to change the source data.

Keywords such as: and, like, case, etc. are case-sensitive.

1. Get field value - TEXT
'CreatedBy.Role.Name'
 as the field name contains a dot, always use ' before and after the field name

2. Set a text value - TEXT
"RoleName"
 always use " before and after the value for text

3. Get current date - DATE
now() 
returns the current datetime in UTC

4. Get the first 18 characters - TEXT
substr('RECORD_ID', 1, 18)
 use substr() function

5. Get the first 18 characters with len() - TEXT
substr(UltimateParentPath, len(UltimateParentPath)-17,18)
use len() function

6. Concatenate text - TEXT
'CreatedDate_Year' + "-" + 'CreatedDate_Month' + "-" + 'CreatedDate_Day'
  use + to concatenate string

7. Concatenate text in case - TEXT
case when isDuplicate is null then 'Name' else 'Name' + " (" +'Username'+ ")" end
 use case function

8. Using multiple when in Case and compare Text - TEXT
case when 'Opportunity.Sales_Type__c' == "A" then "Type A" 
     when 'Opportunity.Sales_Type__c' == "B" then "Type B"  
     else "Type C" 
end
 use == to compare equal and use != to compare not equal

9. Check is Null - TEXT
case when 'Opportunity.Name' is null then "Yes" else "No" end
use is null keyword

10. Check is Not Null - TEXT
case when 'OptySplit.SplitOwnerId' is not null then 'OptySplit.SplitOwnerId' else 'OwnerId' end
use is not null keyword

11. Use && and ! as alternative - TEXT
case when 'Owner.Name' is null && !('Queue.Name' is null) then "Queue" 
     when !('Owner.Name' is null) then "User" 
     else "N/A" 
end
 use || as OR; use && as AND

** You can't use || or OR for the same field, so use IN instead

12. Use && and ! as alternative to get field value - TEXT
case when 'Owner.Name' is null && !('Queue.Name' is null) then 'Queue.Name' 
     when !('Owner.Name' is null) then 'Owner.Name' 
     else "N/A" 
end

use ! as NOT

13. Simple bucketing - TEXT
case 
  when Value == 0 then "[1] 0"
  when Value <= 1000000 then "[2] 0-1M"
  when Value <= 25000000 then "[3] 1M-25M"
  when Value <= 100000000 then "[4] 25M-100M"
  else "[5] 100+M"
end

use <= , < , > , >= for Numeric or Date

14. Get numeric value from field - NUMERIC
case 
  when Type_Data is not null and Type__c == "Type A" then Annual_Data
  when Type_Value is not null and Type__c == "Type B" then Annual_Value
end
else keyword is not a must in case, ' is not a must if the field does not contain dot

 use and or, but not AND OR

15. Check Neglected Case - TEXT
case when DaysSinceLastActivity >= 60 then "true" else "false" end

16. Check Is Lost - TEXT
case when 'IsClosed' == "true" && 'IsWon' == "false" then "Yes" else "No" end
there is NO BOOLEAN type in Einstein Analytics, so always enclosed wth "

17. Using IN - TEXT
case when 'Opportunity.StageName' in ["Stage 1", "Stage 2", "Stage 3", "Stage 4"] then "true" else "false" end

case 
  when AnnualRevenue < 1000000 then "Low"
  when AnnualRevenue in [1000000..2000000] then "Medium"
  when AnnualRevenue > 2000000 then "High"
end

q = foreach q generate 'Account.Name' as 'Account.Name', 'Account.Owner.Name' as 'Account.Owner.Name', (case when !('Account.Owner.Name' in ["Adam Lee","David Lee"]) then "Someone else" else "Adam or David" end) as 'Owner Check';

 use in[ ] and ! function

18. Check is Overdue - TEXT
case when ('IsClosed' == "false") && (daysBetween(toDate(substr('ActivityDate', 1, 10), "yyyy-MM-dd"), now()) > 0) then "true" else "false" end
 use daysBetween(date1, date2) to return the number of days between two dates; if date1 is after date2, the number of days returned is a negative number.

 use toDate() to convert a string or epoch seconds to date -- this sample convert from a string

19. Get Days Overdue - NUMERIC
case when 'IsOverdue' == "true" then daysBetween(toDate(substr('ActivityDate', 1, 10), "yyyy-MM-dd"), now()) else 0 end

20. Check is between 2-30 days - TEXT
(case when date('TIMESTAMP_DERIVED_Year', 'TIMESTAMP_DERIVED_Month', 'TIMESTAMP_DERIVED_Day') in ["30 days ago".."2 days ago"] then "yes" else "no" end)
use date() to return date with input of a year, a month, and a day dimension

21. Check is Yesterday - TEXT
(case when date('TIMESTAMP_DERIVED_Year', 'TIMESTAMP_DERIVED_Month', 'TIMESTAMP_DERIVED_Day') in ["1 day ago".."current day"] then "yes" else "no" end)

22. Check is Past Due - TEXT
case when IsClosed == "false" && (toDate(CloseDate_sec_epoch) < now()) then "true" else "false" end

 use toDate() to convert a string or epoch seconds to a date -- this sample convert from a _sec_epoch

 
23. Duration in Second - NUMERIC
date_diff("second", toDate(ValidFromDate_sec_epoch), now())
use date_diff() to return the amount of time between two dates, the time interval could be: year, month, quarter, date, week, hour, minute, second

24. Check Is Closed - TEXT
case when daysBetween(toDate(ActivityDate_sec_epoch), now()) >= 0 then "true" else "false" end

You also can use date_diff() for the above formula

case when date_diff("day", toDate(ActivityDate_sec_epoch), now()) >= 0 then "true" else "false" end

For more info between daysBetween() with date_diff(), checkout this blog

https://www.simplysfdc.com/2025/03/crm-analytics-calculate-duration.html

25. Get days since last activity - NUMERIC
case    
   when LastActivityDate is null then daysBetween(toDate(LastModifiedDate_sec_epoch), now())   
   when LastModifiedDate > LastActivityDate then daysBetween(toDate(LastModifiedDate_sec_epoch), now())   
   else daysBetween(toDate(LastActivityDate_sec_epoch), now()) 
end

26. Get Past Due Date - NUMERIC
case when IsClosed == "false" && (toDate(CloseDate_sec_epoch) < now()) then daysBetween(toDate(CloseDate_sec_epoch), now()) else 0 end

27. Get Opportunity Age - NUMERIC
case when IsClosed == "false" then daysBetween(toDate(CreatedDate_sec_epoch), now()) else daysBetween(toDate(CreatedDate_sec_epoch),toDate(CloseDate_sec_epoch)) end

28. Get Lead Age - NUMERIC
case when ('IsConverted' == "false") then daysBetween(toDate(CreatedDate_sec_epoch), now()) else daysBetween(toDate(ConvertedDate_day_epoch), toDate(CreatedDate_day_epoch)) end

29. Get Case Duration - NUMERIC
case when ('IsClosed' == "true") then ('ClosedDate_sec_epoch' - 'CreatedDate_sec_epoch')/86400 else ('CurrentDate_sec_epoch' - 'CreatedDate_sec_epoch')/86400 end

30. Get Opportunity Age - NUMERIC
case 
when IsClosed == "true" && CloseDate_day_epoch - CreatedDate_day_epoch > 0 then CloseDate_day_epoch - CreatedDate_day_epoch
when IsClosed == "true" && CloseDate_day_epoch - CreatedDate_day_epoch <= 0 then 0
else round((date_to_epoch(now()) - CreatedDate_sec_epoch) /86400,0)
end

in the above sample, we check:
1). if the opportunity is Closed and Closed Date is after Created Date, then Age = Closed Date - Created Date
2). if the opportunity is Closed and Closed Date is before or equal Created Date, then age = 0
3). if the opportunity is Open, then age = now() - Created Date

Another option to calculate the age for (1) is to use date_diff() function:
date_diff("day", toDate(CreatedDate_sec_epoch), toDate(CloseDate_sec_epoch))

Please note that limitation of the maximum amount of time returned from date_diff() is 9,223,372,036,854,775,807 nanoseconds or 106,751.99 days (> 292 years).

 use date_to_epoch() converts a date to epoch seconds.

 use round(n [,m]) return the value of n rounded to m decimal places, m can be negative.

31. Converting Created Date to PST - DATE
toDate('CreatedDate_sec_epoch'-3600*8)

32. Using starts_with(), ends_with, and lower() to compare string - TEXT
case
  when starts_with(lower(Subject),"call") then "Call"
  when ends_with(lower(Subject),"call") then "Call"
  else "Others"
end

 the one contain full string must be at left, it is case sensitive, so use lower() to help

33. Use matches() for contain - True/False
case when "abcd" matches "abc" then "found" else "not found" end

 the full sentence must be at left, this operator is not case-sensitive, requires at least two characters

case when 'Product_Name' matches "cable" then "found" else "not found" end 

 this will work

case when "cable" matches 'Product_Name' then "found" else "not found" end 

 this is not allowed with error Invalid function argument: 'Product_Name', the second operand must be text.

case when !('Product_Name' matches "cable") then "a" else "b" end 
 use ! as not

34. Use like() for contain - True/False

Customer_Name like "%ni%"

This query matches names that contain "ni" such as Anita Boyle, Annie Booth, Derek Jernigan, and Hazel Jennings.

Customer_Name like "ne%"

This query match returns names that end with "ne".

!(Customer_Name like "%po%")

Use with ! to exclude records. above query shows all customer names that don’t contain "po".

35. Use index_of() to return position a character - NUMERIC

index_of("Hawaii", "a") --> return the first occurrence of "a", starting at the beginning --> result is 2

index_of("Hawaii", "a",1,2) --> return the second occurrence of "a", starting at the beginning --> result is 4

index_of("Hawaii", "a",3) --> return the first occurrence of "a", starting at the third position --> result is 4

The function returns 0 if the search string is not found.

36. Use substr() to return a substring that starts at the specified position - TEXT

substr("CRM", 1, 2) --> 2 characters long, starting at position 1 --> "CR"

substr("CRM", -2, 2) --> 2 characters long, starting from the *end* of the string --> "RM"

substr("Salesforce", 3) --> the first 3 characters --> "Sal"

Use case: find the country of the following San Francisco, CA, United States

substr(fieldname, -1 * (len(fieldname) - index_of(fieldname, ",", 1, 2) - 1))

37. Relative Date filter

CreatedDate >= "LAST_N_DAYS:90"

38. Numeric to Text

In a scenario where you need to use a numeric field for a chart or list widget, you can't do it; unless to convert the field to text.

case when 'CaseHistory.OwnerChangeCount' is null then "00" else number_to_string('CaseHistory.OwnerChangeCount', "00") end

39. Filter by Date

In a scenario, we would like to filter only records with created dates after Sep 1, 2024; we will use dateRange() and put the end date as far as possible.

date(CreatedDate_Year, CreatedDate_Month, CreatedDate_Day) in [dateRange([2024,9,2], [2029,12,31])]

40. Day of the Week

To get which day of the week, the following formula will return 5 for Thursday

day_in_week(toDate(CreatedDate_sec_epoch))

41. MOD

MOD or modulo is the remainder when dividing. For example, “5 mod 3 = 2” which means 2 is the remainder when you divide 5 by 3. In Excel would be MOD(5,3) = 2, but in SAQL

5 % 3 ---> 2

Reference:

• Arithmetic Operators
• Comparison Operators
• Logical Operators
• String Operators
• case
• date_diff()
• Date Functions
• Day in the Week, Month, Quarter, or Year
• Duration between Date or Date/Time Fields