Archive

Posts Tagged ‘DateAdd’

Custom SQL data connections in Tableau

July 12th, 2012 6 comments

Recently I have had to reshape text files using custom SQL in the data connection. Being reasonably competent in SQL I thought this would be easy. Unfortunately I was wrong. Tableau uses the MS Jet data engine which has slightly different syntax to standard SQL used in SQL server. For example when using the DATEADD function the syntax differs. With DATEADD the syntax also differs between the Custom SQL connection and the Tableau calculated fields. This link could also help with the datepart intervals.

Also the CASE statement isn’t recognised by MS Jet SQL, instead an alternative has to be used, such as nested IIF statements. This post I found suggesting CASE alternatives, written back in 2006, suggests CHOOSE can be used instead, it all depends on how the CASE statement was going to be used. Read more…

Categories: Tableau Tags: , , , ,

Omniture visits report in Tableau – part 1

To recreate the Omniture visits report in Tableau was quite difficult so I’ve broken the post into 2 parts. The result will be published on Tableau Public and I’ll supply the link to the report at the end of the post.

I spent some time recently trying to replicate web traffic visit data from Google Analytics in the style of the Omniture visits report. I like the Omniture visits report as it shows how you’re tracking in the current month vs how you were tracking exactly 4 weeks earlier – i.e. compares Monday with Monday, Friday with Friday, etc. It also shows how you were performing vs last year. Read more…

Tableau DateAdd in Custom SQL Data Connection

In Tableau calculated fields to add weeks to a date the DATEADD function is used. Confusingly the syntax to write a DATEADD in Tableau calculated fields differs the the DATEADD in Custom SQL.

The syntax is DATEADD(‘week’,NumWeeksToAdd,DateToAdd). For example in a calculated field DATEADD(‘week’,4,#1 Jan 2011#) = #29  Jan 2011#.

I tried to use this syntax in the CustomSQL area of a data connection and it threw an error. This confused me for a while until I realised that when it comes to CustomSQL the syntax of the statement needs to be SQL syntax and not Tableau. SQL doesn’t recognise ‘Week’ in the DATEADD function. In SQL the week is represented by ‘ww’ or ‘wk’.

To make DATEADD work in the CustomSQL connection the syntax needs to be DATEADD(‘ww’,NumWeeksToAdd,DateToAdd). The full dateadd SQL syntax is available on the MS website at this link: http://msdn.microsoft.com/en-us/library/ms174420.aspx

NOTE: The SQL syntax won’t work in a  Tableau calculated field – Tableau syntax must be used. Click here for Tableau date syntax.

Custom SQL in Tableau is different to many other forms of SQL, some of which is described in this article explaining some of the quirks of Tableau Custom SQL.

Categories: Tableau Tags: , , ,