Archive

Posts Tagged ‘date’

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: , , , ,

Date Hierarchies in Tableau

May 31st, 2012 10 comments

I recently had a request from someone who wanted to be able to drill into their data both from Year – Month- Day AND Year – Week – Day. The first of these is very simple as it’s more or less a Tableau default hierarchy from any date field, although the default also includes quarter (Year – Quarter – Month – Day is the default).

Adding Week into this adds a significant degree of complexity for the simple reason that weeks generally don’t span months in a neat way – i.e. it’s only on very rare occasions that the first of the month is also the first day of the week and the last day of the month is the last day of the week. Almost always the end of a month and the beginning of the next will be in the same week number – in other words some weeks will span 2 months.

The first challenge is to create the Year – Week – Day drilldown. To do this a hierarchy actually has to be manually built. In a previous post I described how to build a hierarchy. Making use of calculated fields it’s possible to build on the hierarchy concept to build a Year – Week – Day date hierarchy. Read more…

Tableau 6 Data Blending With Dates

May 10th, 2012 10 comments

I’ve just had a frustrating hour trying to work out why my attempts to blend data between an Excel file and a data extract wasn’t working. I was trying to blend on 2 fields – a text field called Metric, with the same field name in both datasets, and a date field called Date in the spreadsheet and InvoiceDate in the extract.

Tableau was clever enough to join the Metric fields automatically but I had problems joining the Date fields. I thought it would be a simple telling Tableau to join where Date = InvoiceDate but unfortunately there were a few more steps involved. Read more…

Tableau Dynamic Linked Parameters

November 26th, 2011 3 comments

The first thing to tell you is this isn’t possible, one of the few weaknesses in Tableau.

I wanted to set up 2 parameters in Tableau, the first would be a start date, the second an end date. What I wanted to do should be straightforward – to set the values available in the end date based on what was selected in the start date parameter – so the earliest date available in the end date parameter is the same as the value selected in the start date parameter.

The first thing I did was create a calculated field to return values based on the result of the start date parameter – i.e. If  Date >= Start Date parameter Then Date End. I used this calculated field to set the min and max date range in the parameter and thought that would be parameter set up complete. Read more…

Quickest way to remove the time from a datetime in SQL

November 10th, 2011 No comments

In an earlier post I showed some ways to remove the time portion from a date, but the obvious follow on from that is which is fastest. To test I ran both versions on a dataset of 1.9m rows of data.

The DATE datatype returned the records in 15m 35s = 935s

Getting the data using the DATEADD and DATEDIFF technique took 16m 50s = 1020s

In this example using the DATE datatype was 10% faster – which is significant. From this test the best way to remove the time from a datetime in SQL is by using the DATE datatype.

I’m also aware of techniques to remove the time using text functions. DO NOT USE THESE – they are not handled in an optimised way by the SQL engine and will be far slower than the 2 techniques I have documented.

Categories: SQL Tags: ,

Access 2007 Calendar control

July 18th, 2011 1 comment

I was developing in Access 2007 for the first time today (I was using Access 2000 the last time I developed in Access) and found a nice thing for entering dates into a text box. If you format the text box as a date it automatically shows a calendar control so the user can select a date from the calendar removing data entry formatting errors. So much easier than using the Active X calendar control in previous versions of Access. I was so glad/surprised to see it I’ve felt the need to write a post about something released 4 years ago 🙂

Omniture visits report in Tableau – part 2

This post is a continuation of part 1 on how to create the Oniture visits report in Tableau. Click here to go back to part 1

This is the chart we are trying to create, the same as the Omniture calendar month visits report.

Now we can begin to create the chart. Drag the Day pill to the Columns shelf and the calculated field SelectedMonthVisits on to the Rows shelf. Make sure the Day pill is displaying the DAY(Day). Read more…

Tableau Server Parameter as a Month Filter

In a previous post about dashboard level filtering in Tableau I used an example of creating a month parameter to act as the filter. For this post to make sense please reference the previous post at the link.

The parameter worked perfectly in production, in the Tableau software, but when published on the server for some reason it didn’t work. It might have something to do with me being in the UK and the server I use in the US and the dates aren’t compatible, US being mm/dd/yy and the UK using dd/mm/yy – but I don’t know if this really is the problem or whether it could be that the parameter set up as a ‘Date and time’ appears to Tableau server as a text field because of the date formatting. In the example I format the date to display as the month name. (Custom format = mmmm gives a date format of January, February, March, etc.) Read more…

Tableau Dashboard Level Filter

June 10th, 2011 4 comments

Often in tableau you’ll find you need a filter that’s neither local of global, you will want a dashboard level filter. On a dashboard local filters are often too restrictive as they only alter 1 part of the dashboard, and to have 2 identical local filters on a dashboard also isn’t a valid option. To overcome that problem filters can also be global. This also comes with problems every worksheet from that data source is also forced to share that filter when you often don’t want to.

Currently there isn’t a dashboard level filter – i.e. a filter that’s local to everything on the dashboard only without affecting any other dashboards created from the same data source. Read more…

SQL First Day of Month

May 19th, 2011 1 comment

This post describes how to find the first day of a month in SQL.

The code is: DATEADD(mm,DATEDIFF(mm,0,DateToRemoveTime),0)

A full explanation of how this works is in a previous post describing how to remove the time portion of a datetimein SQL as the concept is exactly the same except where removing the time uses the day (dd) in the above date functions, to get the first day of the month you use the month (mm) instead.

Categories: SQL Tags: ,