In Tableau calculated fields use the DATEADD function to add weeks to a date. 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 Tableau data connection and it threw an error. Eventually I discovered CustomSQL uses MS Jet SQL syntax, not Tableau SQL. SQL doesn’t recognise ‘Week’ in the DATEADD function. To represent the week in MS Jet SQL the correct syntax is ‘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, SQL syntax doesn’t work in a Tableau calculated field; use Tableau syntax in a Tableau calculated field. 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.