Archive

Author Archive

Add an All to a parameter in Tableau

November 12th, 2012 6 comments

EDIT 3/10/16: A different and simpler way to add All to a parameter in Tableau is written in a new article.

 

I have to thank Ben Sullins for this and his post on how to add an All to a parameter in Tableau, finding that article saved me a lot of time.

To begin create the parameter and add an ‘All’ option. Display the parameter on your worksheet.

Next drag the field you want to filter to the filters shelf.

To filter the field using the parameter right click on the field on the filters shelf and select Filter, then Condition.

In the By Formula box enter the following formula:

IFNULL([FilterField],’Null’) = IF [Paramter] != ‘All’ THEN [Paramter] ELSE IFNULL([FilterField],’Null’) END

How does this formula work? Read more…

Categories: Tableau Tags: , , ,

Tableau Dashboard size for ipad

July 21st, 2012 No comments

Recently I’ve been experimenting in Tableau with dashboard sizes for the ipad. I prefer to set it up landscape viewing and found the optimal size is 1020 x 610 pixels, this more or less seems the perfect size to fill the full screen and remove the need for scrolling.

Tableau Extracts are Slow to Refresh on the Server

July 19th, 2012 1 comment

On many occasions I have found Tableau data extracts are slow to refresh on the Tableau Server when pulling from SQL (and I’m sure this happens with all data connections). I always used to put this down to the slow execution of the query, which could definitely be a cause as if it’s slow to run on the SQL Server it will be slow to run when refreshing an extract. It also appears that Tableau can be the cause of the slow updating in some cirucmstances. I have just come across this write up on extract performance by one of the Tableau staff which they put on their forums. I think it is very useful so I’ve copied it into this article.

Basically it advises what to check to see what the cause is of the slow updating of extracts and advises what to do if the blame is on the Tableau side. Read more…

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

Tableau Show or Remove ALL from Autofilter

June 6th, 2012 3 comments

To remove the All option from a Tableau autofilter is possible by following these simple steps:

1. Click the small down arrow in the autofilter heading
2. Scroll down to Customize
3. Uncheck where it says ‘Show “All” Value’ so there’s no longer a tick mark against it.

To show the All option follow steps 1 and 2, and in step 3 check the ‘Show “All” Value’ so the tick mark appears against it, as in the diagram below.

Show All option in Tableau autofilter

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 Dynamic Sorting

May 28th, 2012 19 comments

Recently I created a tabular report where it was a requirement for the user to be able to sort the list by all of the different measure columns. I generally publish my reports on to Tableau Server where sorting is easy by using the Tableau toolbar but I wanted to see how to do this without using the toolbar.

I began to think about ways to do this using parameters and calculated fields as a Dimension has the option of Sort By Field. Luckily I didn’t have to think to hard about how to do this as I stumbled across a blog post which describes how to set up dynamic sorting with Tableau. I slightly modified the more complicated of the 2 techniques described in that article, which I describe here.

For this example I’m going to use the Superstore Sales sample data Tableau provide and create a table showing the Customer State on the rows and the measure values Profit, Profit Ratio and Sales in the columns. If unsure how to do this check my other post on displaying data in tableau as a table. Read more…

Tableau Filter the data on display and not the underlying data

May 16th, 2012 No comments

I was recently set a challenge to filter a chart based on something that happened in the past, but still to show all time periods. The specifics of the task was to look at the number of employees a company had in 2010, and depending on that amount either show or hide all years for those companies that had the right amount of employees. In other words create a ‘Number of employees in 2010’ filter.

The dataset contained only 3 fields, Company, Year and Employees.

Initially it seems simple. Create a calculated field to calculate the number of employees the company had in 2010 and use that as a filter. This is what I did, the field was called [2010 Employee Count] and the formula I used was SUM(IF [Year] = 2010 THEN [Employees] END). Next I had to set up the visualisation to test what I created worked.

I dragged the Year to the columns shelf, Employees to the rows shelf and summed it, the Company to the colour shelf, giving me a line graph with annual employee counts over time split by company. This gives me the following line chart:

Next I tested the filter by dragging the calculated field [2010 Employee Count] to the filter shelf and using the At Least filter to show all companies with over X employees in 2010. This is where it became evident that this wasn’t going to work – the filter affected the entire dataset and not only the selected companies as can be seen below: 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…

Info About Extracts on Tableau Server 6

May 3rd, 2012 No comments

My current employer is still using Tableau Server 6.1 – or 6.1.6 to be precise. I’ve been trying to work out how the extracts work when pulling from a SQL Server so the same extract can be shared by multiple dashboards. The reason for this is that some of my extracts take a long time to update which is not ideal when the same extract is used in multiple dashboards and instead of refreshing it multiple times for each dashboard it would be more efficient to update just once and all dashboards using it will be updated together.

What I was wondering was if when you connect to a data extract in Tableau is whether the published extract is updated when any dashboard based on this extract is updated. It seems this isn’t the case. Once the extract is published it remains as it was a publication time for all time I believe. It appears that when you connect to that extract and publish the workbook Tableau actually duplicates the extract and updates this duplicate whenever the dashboard is updated. In other words the extract published individually to the server doesn’t alter but the duplicate Tableau made embedded in the dashboard does update. Read more…