Meliorainsights.com
  • Home
  • Tableau CRM
  • Field Notes
  • Non-Profits
  • Our Clients
  • About

Meliora Insights, LLC

​Data Cloud | CRM Analytics  (TCRM / Einstein Analytics) Consulting
​meliora - 'for the pursuit of the better'


A blog series distilling quantitative concepts /use-cases in CRM Analytics  (Einstein Analytics).

Picture

that dreaded Einstein Date Transformation  :-)

3/3/2021

0 Comments

 
To calculate the elapsed # of days between a date field in an Einstein dataset and a string date field, use the daysBetween and toDate functions. The number multipliers have to do with epoch seconds.. Thanks for Pedro Gagliardi for his blog "Mastering Dates on Einstein Analytics using epochs". Here are the calculations.

1 hour = 3600 seconds
1 day = 86400 seconds
1 week = 604800 seconds
1 month (30.44 days) = 2629743 seconds
1 year (365.24 days) = 31556926 seconds

Here is a compute expression transformation in a data flow which is of type number.:

daysBetween( toDate('hdr2OLI.user2Oppty.CreatedDate_sec_epoch'),toDate(( (   string_to_number( substr("09/01/2020",1,2))  -   1   )    *   2629742   )    + (  (   string_to_number(substr("09/01/2020",4,2))   -  1) * 86400    ) + ( (string_to_number(substr("09/01/2020",7,4) )-1970) *   31556926) ) )



0 Comments



Leave a Reply.

Site powered by Weebly. Managed by Hostwinds
  • Home
  • Tableau CRM
  • Field Notes
  • Non-Profits
  • Our Clients
  • About