Handling Date/Time columns for various Plugins
1. How Metric Insights determines which columns are Date/Time types
For various plugins such as for Tableau, QlikView, Qlik Sense, MicroStrategy and others, Metric Insights pulls data from those sources via CSV export. This means that Metric Insights must infer the data types for each column in the CSV. It does not have any type information in the CSV; it only has the data. So Metric Insights looks at all the values and infers the data type.
In most of the apps such as Tableau, QlikView, Qlik Sense you can manually export the CSV and assume that that is how Metric Insights will be getting the data. A few exceptions do occur.
For example, with Tableau you can use the UI controls in Tableau Server to export and download a CSV. However, Metric Insights pulls the data via the view url of Tableau Server using CSV export. E.g.,
https://tableau.example.com/views/workbookName/sheetName?format=csv Metric Insights then infers the data type of each column in the exported CSV by interrogating the values. For Metric Insights to interpret a column as containing date/time values, it needs to see that the values 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 type the whole column as a Text field rather than a Date field. To prevent this from happening you will need to ensure that all values in the column are of date/time format.
2. For plug-ins that use the Query Builder
Most of the plugins such as Tableau, QlikView, Qlik Sense, MicroStrategy use the Query Builder.
Use the Visual Editor link in the Element Editor (Metric or Report) to bring up the Query Builder. The Query Builder screen shows the column interpreted as date/time with Type DATE.
3. The various date/time formats that Metric Insights looks for
This is just 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. Metric Insights needs at a minimum the Year and Month for a Date/Time field
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. How you know that Metric Insights has recognized the Date/Time correctly?
When you use the button to Validate your plug-in command query in the editors for either a Metric or a Report, a sample result set is displayed and columns that are interpreted as date/time will display in YYYY-MM-DD hh:mm:ss format. For example:
4.1. Date/Time recognized in Metric
4.2. Date/Time recognized in Report
Use the button to Validate your Tableau plug-in command query in the Report editor, and the sample returned data shows the column interpreted as date/time in YYYY-MM-DD hh:mm:ss format
5. If Metric Insights is not able to determine your date field as valid, then use these options to coerce
5.1. Use the override feature in Query Builder
5.2. Filter out rows that are not Date/Time
If your result set is returning empty values:
In Query Builder use the Add filter button to create a condition
5.3. Construct a Composite Date/Time field in the plug-in request
If your date/time value is spread over several columns, then you can build a Composite date/time column. In the above example, use the control in the Visual Editor to construct a Composite date from the individual year, month, day columns in the your view.
5.4. Use an Intermediate Report in Metric Insights, manipulate the Date/Time there
You can create a Report from the your plug-in , and then create other Metrics (or Reports) from this newly created Report. Be sure to save the report as a myslq SQL table.
Then you can simply write SQL against the report and apply any SQL functions you want for converting or formatting data from the report into date/time columns as appropriate. See the help documents on making an Existing Report.
5.5. Make the changes in the BI app that sources the data to Metric Insights
Sometimes the best way to fix the format of the dates 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.