CRM Analytics: Recipe syntax

Every computer language has its own syntax, the same with query language or formula. Here are the samples of SAQL that are used in Dataflow.

 Since CRM Analytics is moving with Recipe, here are samples of the formula syntax in the Recipe:

String - use '', e.g., 'Singapore'

Field name - use "", e.g., "Account.Id", optional if the field name does not contain dot(s)

Field with blank value - "Opportunity.Id" IS NULL

CASE, e.g., case when Have_Activity = 'Yes' or Have_Opportunity = 'Yes' then 'No' else 'Yes' end

IN with (), e.g.,
case when Type in ('Customer', 'Other') then 'A'
        when Type in ('Customer - Direct', 'Customer - Channel') then 'B'
        else 'C' 

end

Logical Function
use or, and, e.g., case when Have_Activity = 'Yes' or Have_Opportunity = 'Yes' then 'No' else 'Yes' end

Compare Operator
use != (not <>) as compare operator, also use = (not ==), e.g., Have_Activity != 'Yes'

Current and Previous Quarter

case 

    when quarter(now()) = 2 and (year(now()) = year(LastActivityDate)) then 'Yes' 

    when quarter(now()) = 3 and (year(now()) = year(LastActivityDate)) and (quarter(LastActivityDate) in (2,3)) then 'Yes' 

    when quarter(now()) = 4 and (year(now()) = year(LastActivityDate)) and (quarter(LastActivityDate) in (3,4)) then 'Yes' 

    when quarter(now()) = 1 and ((year(now()) = year(LastActivityDate)) or (quarter(LastActivityDate) = 4 and year(LastActivityDate) = year(now()) -1)) then 'Yes'

    else 'No'

end

Do you have a great formula to share? Feel free to add in the comment.