Join our Community in its new Home - The Datorama Trailblazer Community Group!

It's been an amazing 3 years coming together in this forum to collaborate, innovate, support, and inspire each other about our shared usage of Datorama. While this is not quite a goodbye, we are excited to announce that we are getting a fresh start in our new home within the Salesforce Trailblazer Community. We have a ton of fun new content planned and you may even see the revival of some of our most popular posts from the past few years.

We’ll be keeping this group around for a bit for you to peruse, but as of November 15, we will no longer be allowing new posts or comments. Be sure to join our new group at https://www.salesforce.com/success-datorama to keep the conversation going.

We can’t wait to see you there!

What’s your idea of a perfect date?

Nidene_PillayNidene_Pillay LondonSYS_ADMIN IMG42
edited May 2021 in Tips & Tricks
DD.MM.YYYY for me, I truly find the others confusing  :smile:  and I’m pretty sure that we’ve all run into some bad date formats!  :s 
But on Datorama... it can be solved... eventually! (don’t quote me anywhere else)  

Enjoy the cartoon  :smile: and let’s dive into the world of dates in Datorama.




Let’s back track a little and understand the backend of the dates in Datorama and why we need them.
These are stored as Date type elements in the database and allows you to make advanced comparisons between one and another based on date/time intervals.
Date type dimensions can be used in date filters and all date type dimensions are displayed in the same format which can be configured in the workspace settings. 

The default date format in Datorama is “yyyy-MM-dd HH:mm:ss.S”

Datorama recognizes and supports all kinds of common date formats that you can also choose from if needed.
You also have an option to apply an additional date format in case there is more than 1 format in your file, or future files!



However, sometimes you may face different data stream processing errors due to dates and their unique formats so I will give you some ideas on how to tackle the exceptions below.
 It’s best to make amends in the mapping section of your Day dimension field.

1) Unparsable date: “value does not match its declared type.” or “Invalid date format

This can be solved using a PARSEDATE formula.
  • Eg1: var date = EXTRACT(csv['Date'],' ',1); PARSEDATE(date,'dd-MMM-yy')
  • Eg2: PARSEDATE((FORMATDATE(csv['Date'],'MM/dd/yyyy')),'dd/MM/yyyy')
  • Eg3: PARSEDATE(FORMATDATE_DEFAULT(csv['Date']), 'yyyy-dd-MM HH:mm:ss')

2) Un-identifiable format: Perhaps you have files with some blank cells in the date column? You can add this formula to the Day dimension:

if(!ISEMPTY(csv['Date'])){

PARSEDATE(LEFT(csv['Date'],10),'yyyy-MM-dd');
}
else {
null;
}

3) When there is no date in your file, you can add TODAY(); to the Day dimension or use a DATEADD function.

4) Inconsistent date format:
  • Using the latest TotalConnect version, the date could be recognized by the system as a text (preview section), and you can manipulate it in the mapping section to parse the date.
  • Eg: In this case PARSEDATE(EXTRACT(csv['date'],'-',0),'d.M.yy') will parse the below date format.


Some useful success center articles on date formats and functions:
  1. Date format syntax
  2. PARSEDATE vs FORMATDATE functions
  3. Formulas in Datorama
Now let’s look at some useful hacks that you could do with dates!

1) If you need to Extract a date from file name:
Eg: Filename: 1475016970372_Ross 2016 - On Target F18-54_v3.1-Daily Auto Delivery_2_340 27-SEP-16 Upi
You can use the parsedate formula and extract from the right hand side.

PARSEDATE(RIGHT(env['filename'],9),'dd-MMM-yy')

2) ‘Manual’ data load rule:
This allows you to apply a syntax in the Day field of the data stream and upon a certain condition, the formula returns null, meaning that these rows are not uploaded into the platform. It is, of course, more complex, and for simple use cases, we would recommend the regular Data Load Rule so that you can keep track of it and not get syntax fever! :)

Eg: IF(csv['impressions']>100, csv['date'], null)

Here’s a few blasts from the past around dates from other community users!
  1. Better date than never
  2. Text dimensions to dates




Goodluck!
Nidene

Comments

Sign In or Register to comment.