Posts Tagged ‘tableau’

Order of operations in Tableau

May 14th, 2013 No comments

I was browsing the Tableau forums recently and stumbled across this mammoth post which began out with a complex request of how to count distinct users over a running time period and generated into a lot of detail about the internal workings of Tableau. It’s one of the most useful threads I’ve read, hence I felt I needed to copy the information about the order of events/operations within Tableau and put it here.

Massive thanks to 2 world renowned Tableau experts, Joe Mako and Jonathan Drummey, who put this together.

  • Tableau generates the SQL query/ies (typically one per datasource) and packs as much of the calculation, filtering, & computation as it can into them.
  • Inside Tableau, filters on aggregate calcs are applied.
  • Densification happens. Densification depends on pill type, pill arrangement, whether domain padding is being requested via Show Missing Values, compute using settings of table calcs, mark types, data structure/density, etc.
  • Data blending occurs.
  • Compute blended aggregates – These are aggregate calcs in one data source that use fields from the other data source.
  • Filters on blended aggregates are applied.
  • Compute table calcs.
  • Compute grand totals & subtotals.
  • Compute total-based reference lines on regular aggregates, and percentile distribution reference lines for regular aggs and table calcs.
  • Apply filters on table calcs.
  • Filter latitude (generated) and longitude (generated).
  • Manually hide marks.
  • Compute all other reference lines.
  • Pages Shelf.
  • Compute Tooltips, Title, Annotations.
Categories: Tableau Tags:

Keep an aggregation static and unaffected by Filter in Tableau

February 10th, 2013 No comments

A question I’ve seen asked many times in different guises is how to keep an aggregation static and unaffected by filters for comparison purposes in Tableau.¬† Dependent on the circumstances of how the data is set up in the background and how the visualisation needs to be filtered is how the solution is derived. Unfortunately I can’t be clearer than that, as everybody who’s familiar with Tableau knows every solution is different – and there are often multiple techniques available to reach the same goal.

In this post I’m going to detail what I feel is the simplest way to do a comparison of individual values against an aggregated value – for example how an individual stores sales compare against a group average, or how a regions new business leads compare against the entire country.

The key to this is to have the aggregation not affected by the filtering to allow for comparison. Read more…

Tableau Dimensions as Columns in a Table

December 18th, 2012 7 comments

If you have created tables in Tableau you might have noticed once you get to a six dimension columns  in the table the left hand columns begin to merge into one column. This only happens when using dimensions as column headers, not measures as when displaying multiple measures in a table this can be achieved by putting only 1 pill, the Measure Names, on the shelf. If you want to know how to add measures read my article describing how to display multiple measures in a table.

Tableau is not really designed for showing tabular data but sometimes it’s useful to show line item data as part of a drill down in a table, or perhaps you need to create a report specifically with the purpose of exporting Tableau to Excel. Read more…

Tableau Server Permissions Best Practice

November 29th, 2012 No comments

If you have had to use Tableau Server in medium to large environments it’s likely you have had to deal with Tableau Server permissions . Initially I found it wasn’t the most logical way of assigning permissions, I had to do a lot of repetitive tasks.

The thing to keep in mind is that Tableau Server permissions work at each level and can require assigning at each level – i.e. view => workbook => project – but if you assign permissions from the workbook level or below, if the permissions differ for many workbooks/views in the project maintenance becomes increasingly time consuming.¬† I have found the most efficient way to assign permission is from the top down – i.e. project => workbook => view – and I’ll explain the reasons later in the article. Read more…

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…