Einstein Analytics: computeExpression return Date

In some scenarios, we need to return a Date field from computeExpression node. 

Date value can be null

sample:

case when ClosedDate_sec_epoch > date_to_epoch(now()) then toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z") end

or

case when ClosedDate_sec_epoch > date_to_epoch(now()) then toDate(ClosedDate_sec_epoch) end

Date or Date/Time

For a Numeric field, Precision and Scale are required, while for a Date field, Date Format is required in computeExpression. Einstein Analytics by default will "explode" a Date field into:

- Year

- Quarter

- Month

- Week

- Day

- Hour

- Minute

- Second

- Epoch days

- Epoch seconds

Let us see some samples of computeExpression returning Date type. In this sample, ClosedDate is a Date/Time field in Salesforce:

(1) SAQL Expression: toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z")

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

--> this will create DateTime field in Einstein Analytics

(2) SAQL Expression: toDate(ClosedDate_sec_epoch)

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

--> this will create DateTime field in Einstein Analytics

(3) SAQL Expression: toDate(ClosedDate,"yyyy-MM-ddTHH:mm:ss.000Z")

Date Format: yyyy-MM-dd

--> this will create Date field in Einstein Analytics

(4) SAQL Expression: toDate(ClosedDate,"yyyy-MM-dd")

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

--> this will cause an error because ClosedDate in this sample is a date time.

(5) SAQL Expression: toDate(ClosedDate_sec_epoch)

Date Format: yyyy-MM-dd

--> this will create Date field in Einstein Analytics, similar to (3)

(6) SAQL Expression: ClosedDate

Date Format: yyyy-MM-dd

--> this will cause an error, we can't use the field directly, even it is a date field 

(7) SAQL Expression: now()

Date Format: yyyy-MM-dd

--> this is okay, it will return the UTC date

As the screenshot above, the date format determines how the values are stored in Einstein Analytics. But, let us drill further:

Even hour, minute, and second are not added in the date format then not shown in the date field, Einstein Analytics still maintains hour, minute, and second in the exploded fields.

yyyy-MM-ddTHH:mm:ss.000Z is similar with yyyy-MM-ddTHH:mm:ss.SSSZ and similar with yyyy-MM-dd'T'HH:mm:ss.SSS'Z'

Now, let us try to use Date field from Salesforce, instead of Date/Time field. CloseDate here is a Date field in Salesforce:

(1) SAQL Expression: toDate(CloseDate,"yyyy-MM-dd")

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

--> this will create DateTime field in Einstein Analytics

(2) SAQL Expression: toDate(CloseDate_sec_epoch)

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

--> this will create DateTime field in Einstein Analytics

(3) SAQL Expression: toDate(CloseDate,"yyyy-MM-ddTHH:mm:ss.000Z")

Date Format: yyyy-MM-dd

--> this will cause an error, because CloseDate is a date field, not Date/Time field in Salesforce

(4) SAQL Expression: toDate(CloseDate_sec_epoch)

Date Format: yyyy-MM-dd

--> this will create DateTime field in Einstein Analytics

(5) SAQL Expression: toDate(CloseDate,"yyyy-MM-ddTHH:mm:ss.000Z")

Date Format: yyyy-MM-ddTHH:mm:ss.000Z

--> this will cause an error, similar to (3), CloseDate is a date field, not Date/Time field 

(6) SAQL Expression: toDate(CloseDate,"yyyy-MM-dd")

Date Format: yyyy-MM-dd

--> this will create Date field in Einstein Analytics 

In summary:

• for SAQL Expression, it is easier to use toDate(xxxx_sec_epoch) 
• for Date Format, use yyyy-MM-ddTHH:mm:ss.000Z for Date/Time, or use yyyy-MM-dd for Date field

JSON:

If you download the dataflow JSON, you will see an additional \ before " in saqlExpression, but you can simply ignore that in the dataflow UI.

sample:

          "saqlExpression": "toDate('CreatedDate', \"yyyy-MM-ddTHH:mm:ss.000Z\")",

          "format": "yyyy-MM-dd",

Side Note: can we refer to a field in computeExpression within the same note?

Yes, as long as the field referred to is located above (in order) from the field call it, see screenshot below, CloseDate_2 can use CloseDate_1, but not CloseDate_4 and CloseDate_6.