Handling Date/Time Columns
When pulling the data from various plugins such as Tableau, QlikView, MicroStrategy and others, Metric Insights does it via CSV export. The CSV file does not have any markings, defining the type information. This means, that Metric Insights have to define the type of data by inspecting the values. This article describes how Metric Insights define which of the values are of a date/time type.
1. For Tableau
When it comes to Tableau plugin, the User can export and download a CSV via the UI controls in Tableau Server. However, Metric Insights pulls the data via the view url of Tableau Server using CSV export. The URL, for example, can look like this:
https://tableau.example.com/views/workbookName/sheetName?format=csv Metric Insights then defines the data type of each column in the exported CSV by analyzing the values. For Metric Insights to interpret a column as containing date/time values, those values have to conform to a particular date/time format.
NOTE: If some of the values in a column are in date/time format, but other values are not, then Metric Insights will mark the whole column as a "text field" rather than a "date field". To prevent this from happening, ensure that all values in the column are of date/time format.
2. For Plugins Using Visual Editor
Most of the plugins such as Tableau, QlikView, Qlik Sense, MicroStrategy use the Visual Editor (Query Builder).
Click [Visual Editor] in the Data tab of Dataset Editor to bring up the Query Builder. The Query Builder screen shows the column interpreted as date/time with Type "Date".
3. Accepted Date/Time Formats
This is a sample list of the date/time formats that Metric Insights looks for in the CSV file exported from Tableau Server.
"yyyy-MMMMM-dd HH:mm:ss",// 2013-August-01 01:23:45
"yyyy-MM-dd HH:mm:ss", // 2012-01-01 01:23:45
"MM-dd-yyyy", // 01-01-2012
"yyyy-MM-dd", // 2012-01-01
"yyyy-MM", // 2012-01 but not 2012-2013
"M/d/yyyy h:m:s a", // "4/15/2013 1:00:00 AM" for hourly or minute Tableau
"MM/dd/yyyy", // 01/01/2012
"MM/dd/yyyy HH:mm:ss", // 01/01/2012 01:23:45
"MMMMM d, yyyy h:mm a", // "April 15, 2013 1:00 AM" for minute Tableau
"MMMMM d, yyyy h a", // "April 15, 2013 1 AM", "April 15, 2013 12 PM" for hourly Tableau
"MMMMM dd, yyyy", // January 01, 2012
"MMMMM dd,yyyy",
"dd MMM yyyy", // "05 Nov 2013" for Tableau
"MMMMM, yyyy", // January, 2012 --- also catches January, 12 -> January 0012
"MMMMM,yyyy",
"MMMMM yyyy", // January 2012 --- also catches January 12 -> January 0012
"MMM, yyyy", // Jan, 2012 --- also catches Jan, 12 -> Jan, 0012
"MMM,yyyy",
"MMM yyyy", // Jan 2012 --- also catches Jan 12 -> Jan 0012
"yyyy-MMM-dd", // 2012-FEB-01 Oracle style. 12-FEB-12 -> Jan 12, 0012
"EEE MMM dd HH:mm:ss z yyyy", // Wed Aug 21 15:50:21 UTC 2013 3.1. Minimum Requirements
Metric Insights requires at a minimum a Year and a Month in the Date/Time field. In the previous list of formats you can see that Year and Month are required. The following is a list of the date/time parts that are needed as you expand into days, hours and minutes.
- Year, Month;
- Year, Month, Day;
- Year, Month, Day, Hour;
- Year, Month, Day, Hour, Minute.
4. Checking Correct Validation
When using the button to Validate command query in the Editor, a sample result set is displayed and columns that are interpreted as date/time will display in YYYY-MM-DD hh:mm:ss format.
5. Troubleshooting Incorrect Validation
In some cases Metric Insights does not register a column as containing date/time data. In this case you can manually change that.
5.1. Override Feature in Query Builder
When using the Visual Editor, the column data type can be changed via Override feature.
5.2. Construct a Derived Field
In the Visual Editor click [+ Derived Field] and select to create a "formatted date" field.
5.3. Filter Out by Date Picker
- [Manage Filters] in the Data tab.
- [+Add External Filter]
- Complete the Filter and choose the "Date Picker" in Filter is field.
5.4. Make the Changes in the BI App
Sometimes the best way to fix the format of the date is to make changes in the Business Intelligence app that sources the data.
For example, with Tableau, publish your worksheet so that the Date/Time columns conform to one of the date/time formats that Metric Insights understands.
This might be an iterative approach, but based on the examples provided earlier in this article you can format your column in Tableau desktop as one of the recognized date/time formats and publish to Tableau server.