Using Dates in SAQL and Dataflows in Tableau CRM

There are no dates in Tableau CRM.  So, in this blog, I hope to give you some tips on how to work with dates in Tableau CRM.  By the end of this, you’ll have some great examples of how to work with dates in SAQL and in data flows.

Data Types and Apologies

Before I begin, I’d like to make an apology.  I’ve taught quite a few workshops on Tableau CRM.  I’ve been teaching workshops since the product was called Wave.  When I taught these workshops, one of the first things that I would teach people is that there are three data types in a Tableau CRM dataset:

  • Dimensions (think strings)
  • Measures (think numbers)
  • Dates

This seems reasonable, right?  Think about the process of importing a CSV into Tableau CRM.  The wizard has you select the field type and you have three options.


Wrong!

There are really only two data types in Tableau CRM data sets: Dimensions and Measures.  Now you might be thinking, “wait a minute, I’ve seen dates in the product.”  Open the fields panel while building a lens and you see an entire “Dates” section.

That’s just there to trick you.  There are no dates in your dataset.  If you can come to understand that, it will actually make it a lot easier to work with dates in Tableau CRM.


I have two reasons for saying there are no dates in your datasets.  First, the documentation says so.  Second, if you try and put any fields from your datasets into functions that require a date, they will error out.


Let’s take a look at the documentation first.  Check out the Date Functions page of the Tableau CRM SAQL Reference.  “When you upload a date field to Tableau CRM, it creates dimension and measure fields to contain the date and time information. You can use SAQL date functions to convert the dimensions and measures to dates.”


There you have it!  You can upload dates, but the only things that are stored in the datasets are dimension and measure fields.  


To illustrate my second reason, let’s look at some examples of how to work with dates.  You’ll see in all of my examples that the first step in working with dates is to cast your data as a date.

Sample Data

For our examples, we will need some sample data.  I took the following massive CSV and loaded it directly into Tableau CRM.


To illustrate some points I also loaded this massive collection of data into a Salesforce Object:


It is important to note that in my CSV, I have just plain dates and in Salesforce I have a datetime.  It’s very important to know the original format of the date data you loaded into Tableau CRM.

Date Functions in SAQL (CSV)

For my examples, I’m going to try and use the date_diff function.  This function takes in a datepart and then two dates.  So, lets prove there are no dates in our dataset.  To get a feel for the data, run this query: 

q = load "Tickets_csv";

q = foreach q generate 'Date_Opened' as 'Date_Opened', now() as 'Now';


You should get a result like this:

So let’s try to use the date_diff() function.  I think the most common error people make is to try and use the Date_Opened field as a date.

q = load "Tickets_csv";

q = foreach q generate 'Date_Opened' as 'Date_Opened', now() as 'Now', date_diff("day",'Date_Opened',now()) as 'days_open';


If you run that query, you get an error:

You get this error, because Date_Opened is a dimension and not a date and the date_diff function expects a date.  To use this function, you will need to convert Date_Opened to a date.


Running this query:

q = load "Tickets_csv";

q = foreach q generate 'Date_Opened' as 'Date_Opened', now() as 'Now', date_diff("day",toDate('Date_Opened',"yyyy-MM-dd"),now()) as 'days_open';


Yields this result:

The only tricky part about this can be remembering your date format for how you originally loaded all your dates.  In this case, it was "yyyy-MM-dd".  If you don’t want to worry about date formats, I recommend using the epoch field that got created when you uploaded the date field.  


q = load "Tickets_csv";

q = foreach q generate 'Date_Opened' as 'Date_Opened', now() as 'Now', date_diff("day",toDate('Date_Opened_sec_epoch'),now()) as 'days_open';


You can use the sec_epoch field in your toDate function to avoid having to put in a format mask.


Date Functions in SAQL (Salesforce)

Okay, lets run these same queries against a dataset coming from Salesforce.  The first thing to notice is that when loading data from Salesforce, Tableau CRM automatically identifies the data types.  You don’t have to specify them like you did with the CSV upload.  But dates are treated the same way.


Let’s look at our Salesforce data:

q = load "Ticket_SF";

q = foreach q generate 'Date_Opened__c' as 'Date_Opened__c', now() as 'now';

Notice the format of your datetime column.  If you try to use Date_Opened__c directly in a date function, it will fail just like before.  Again, you need to convert it to a date first.


q = load "Ticket_SF";

q = foreach q generate 'Date_Opened__c' as 'Date_Opened__c', now() as 'now', date_diff("day",toDate('Date_Opened__c',"yyyy-MM-ddTHH:mm:ss.000Z"),now()) as 'days_open';



And again, you can use the sec_epoch field to avoid using the datetime format mask.


q = load "Ticket_SF";

q = foreach q generate 'Date_Opened__c' as 'Date_Opened__c', now() as 'now', date_diff("day",toDate('Date_Opened__c_sec_epoch'),now()) as 'days_open';


Date Functions in Dataflows

You might be wondering if you can use these date functions in a dataflow.  You sure can.  The only thing you have to watch out for is the fact that your epoch fields don’t exist yet.  So we cannot do the trick of using the sec_epoch field to skip providing a format mask.  


If I wanted to compute a days_open field from my CSV in a dataflow, I would add a compute expression that used this formula: date_diff("day",toDate('Date_Opened',"yyyy-MM-dd"),now())

The compute node would look like this:


For my Salesforce dataset, things would be very similar:




Conclusion

The most important thing to remember when working with date functions in Tableau CRM is that you don’t really have any dates stored in your dataset.  So, first convert an existing field to a date and then use your date function.  Happy querying!