Two ways to implement ‘Show All’ and ‘Top N’

In this post i’m going to show quickly two methods which I used to create a ‘show all’ option alongside a ‘top n’ option.

Before we start, the visualisations produced in this blog can be found here, so feel free to download them and break them down yourself.

Option 1. Two parameters, one set and one calculated field.

  1. Create your first parameter. This will be our ‘Top N Parameter’ it will be of either float or integer type and should have all the ‘n’ values which you wish your user to select from.
  2. Create your set. This will be based on your dimension that you wish to view the ‘Top N’ of.

    Navigate to the ‘Top’ tab on the create set window, then select ‘By field:’ from the drop down of the numeric value, select the parameter you have just created; and of course select your method for determining your ‘Top N’.


  3. Create your 2nd parameter. This parameter will be used to determine whether the or not we wish to show the ‘Top N’ or we wish to show all. It will be of string type and contain these two text strings.


  4. Create a calculated field which determines whether we should use all the values, or only those that meet the criteria given in the set based on the selection from our 2nd parameter.


  5. Drag this calculated field onto the filter shelf and select to only include those where the result of the calculated field = True



Option 2. One parameter and three calculated fields.

I developed this second idea knowing that the first isn’t the best user experience, ideally you would want your user to be able to select the ‘All’ value from within the Top N drop down.

But this causes a problem, our parameter type must be a string.

Then this causes a problem because our set cannot work off a string field.

So here’s how…

1. Create your parameter. This will be our ‘Top N or Show All’ parameter it will be of string type, and you will have to list the N values which you wish to include, alongside the ‘Show all’ value.


2. Create a calculated field which simply equals the parameter value


3. Convert the field type of this column to be of a numeric type (this will just create a NULL value if ‘Show All’ is selected within the parameter).

4. Create  a second calculated field. This will be used to determine whether the value is within the ‘Top N’.


5. Create your  third calculated field. This will be placed on the filter shelf and will be used to determine whether to return all values or simply those that meet the conditions of the second calculated field.


6. Drag this calculated field onto the filter shelf and select to only include those where the result of the calculated field = True





Using string calculations to create messages for NULL values

Guiding and ensuring your users interpret the data correctly is the key to a successful data visualisation.

So, question. What happens when your users come across NULL values in your visual, as in the instance below?


Best case, they simply understand that there are no recorded sales for that country during that specific month.

Worst case, they come up with some mind boggling conclusion that the Belgium store was forced to close due to declining sales in September 2015.

But how about we ensure the data is interpreted correctly through creating a combined lookup/conditional/string statement.


Tableau introduced a neat workaround in this Tableau knowledge base article, but there a re limitations. We may need to ensure that we distinguish between these NULL values and true zeros. True zeros may exist because we are rounding, or if you are presenting your data in 1,000s, 1,000,000s etc; they may also exist because you are working with a measure value that can be both positive and negative, such as profit, and in turn summing these values may equate to a true zero value.

The method outlined in this blog is a very simple tweek to that outlined in the tableau article, but instead of returning zero, we return text.


The lookup(sum([Sales]),0) is effectively evaluating what the value is of that specific cell (because we are offsetting by 0). Where are cells are empty, this with return the value NULL.

So by wrapping this formula with a isnull() we are quite simply returning a TRUE FALSE dependant on whether the vale is infact NULL or not.

By then using an IF statement we can identify what should happens if the condition is TRUE (i.e. it is NULL), where we return the string “No sales”, and where it is FALSE we return the sum of sales.

A note here is that the data type outputs from an if statement must return the same data type, and as a result we must change the datatype of sum([Sales]) calculation and to do this we simply wrap this in an str() – string- function.

Because our output is now a string and not a number, we as a result loose the ability to format the number, as a number (if that makes sense). This is why I include the round() function, but you may also wish to do some further manipulation such as adding a currency indicator, or thousands separators.


So can we use this to label NULL values on bar charts? The answer is yes, but it is not as simple as placing our new string field on the label shelf and the sum([Sales]) on the rows/columns shelf.


This is because the bars where there are ‘No sales’ don’t actually exist, so there is nothing to label, so we now need to perform a very similar calculation to return a value so there is something to label, I return the value 0, in order to build a mark which we can then label.



And now we have a labelled bar chart 🙂



Switching between discrete and continuous colouring

Ever wanted your users to have the ability to select whether they would like to colour their chart by either a continuous field or through discrete colouring but thought it wasn’t possible?

Well it is!

A use case came up last week and after a brain storm I managed to create this.


Note, this tip relies on a dual axis, so if your chart is already a dual axis then this is not possible (sorry :(), although an alternative would be to switch between your two worksheets dynamically, as outlined here.

  1. Create a parameter (or another element, such as a count) that defines whether the chart should be coloured in a continuous or discrete way.2016-08-15_19-10-49
  2. Create a calculated field which will return either the value [Sales] in my case, or NULL when each variable is selected.2016-08-15_19-13-25.png2016-08-15_19-15-43
  3. Drag these two pills onto the relevant shelf, in my case I wanted to create a horizontal bar chart, so placed the pills on the column shelf.What you will notice now is that as you switch between the values on your parameter, the charts will take it in turns to appear as your values switch between NULL and the appropriate measure.2016-08-15_19-14-28.png2016-08-15_19-14-57.png
  4. Now that we have two axes, we also have two marks cards, one for each axis that we can treat seperately. We can now format them as so. On the axis that appears when I select ‘Sales’ colouring (i.e. by my measure), i simply drag [Sales] onto the colour shelf.2016-08-15_19-19-33.pngWhilst on my secondary axis, I will drag my [Category] field onto the colours shelf.



  5. Lastly is some clean up work, hiding the NULL values indicator, creating your dual axis, and then synchronising your axes.Of course you cannot have your axes showing in this case because one of them will always show 0. In that case it is probably wise to label your chart as an alternative or make the values clear in your tooltips.

Indeed it is also possible to switch between chart types and measures using this method!



Tableau Tip – Using concatenation to sort values in tables and charts

I have a chart (not quite a dream), this chart is broken down by two dimensions ([Category] and [Sub-Category]), and a measure ([Sales]).


But when I sort sales I get a type of what I call, ‘nested sorting’, it is sorting sales, but it is sorting sales within each category (if you have a different number of dimensions, it will sort within the 2nd last dimension on your shelf.


But there is a way to prevent this, I simply need to concatenate the dimension members into one calculation, and place this as the first column, and the hide it.

To achieve this I create a calculated field, but you can achieve an identical result by combining your fields (I don’t know why, but I prefer the former).




Something that I often offer my users when such a requirement exists is a parameter that switches out the table with another sheet when they want to sort, using this great tip by Hashu Shenkar of The Information Lab.

I do this because I prefer the look and feel of the unsorted table, and how it doesn’t include unnecessary dimension members values as they are repeated down the table.




4 ways to prevent date conversion problems when building date strings

Recently I was working on a project which had month and year columns but no actual date field in Tableau.

Simple enough, I created a calculated field which added a day number (01) to the front of the string.

picture 1 blog.png

I then changed the data type to a date and we were away, we now have the ability to use Tableaus date/time manipulation functions.

Little did I know that when I sent my workbook over to the states, because of the way in which I have built my date field, and because of the way Americans read their dates (mm/dd/yyyy) my pretty YTD trend graph was now broken. It now showed just a single point because all of my month values were, as defined by my calculated field “01”.

(damn those pesky Americans and their unconventional metrics).

Picture 2 blog.png

Okay, you could of course just get your users to change their default local settings to the UK region, but this could have further impact on workbooks that rely on the US date settings, and perhaps more importantly what if your audience doesn’t know about the change and as a result miss-interpret the dates.

Well in this blog I will outline four ways in which we can prevent this problem.

  1. Create your dates in the backend (probably not the most helpful tip, but as a result Tableau will automatically now how to read them dependant on the users settings).
  2. Build your calculated field as [Year]+”/”+[Month]+”/01”, this format (yyyy/mm/dd) is independent of regional settings.
  3. Use the above structure (yyyy,mm,dd) with the makedate() function, so in our example Makedate([Year],[Month],1)
  4. Build an epic calculation (or steal the one here), which generates the unique number for the underlying date which can then be converted to a date by either wrapping date() around the formula, or changing the field type to date.Every date since 01/01/1900 is given a unique identifier based on the number of days from this start date (this is called the 1900 system). 01/01/1900 is assigned the value 1 (given that it is the 1st day), 02/01/1900 being 2, etc.As a result it is possible to create a formula that converts month/year values into a full date.

    //Calculate the number of non-leap years between your date and 1900, then multiply this by 365


    //Calculate the number of leap years between your date and 1900, then multiply this by 366


    //Calculate whether the year of the date is a leap year


    //If it is not, then calculate the running total of days up to the month value


    //If it is, then calculate the running total of days up to the month value (this is the same as above but with an added day from February)


    //In excel add 1 to your date to make it the 1st of your month/year value. Due to differences in structure between Microsoft serial system and Tableau serial system (First of all, Tableau actually knows that the 29/02/1900 is not an actual date (LOL Microsoft), secondly the serial in Tableau for the first date in the system (01/01/1900) is 0, whereas as previously mentioned with the 1900 system, it is 1.)


    This calculation was really fun to problem solve and their were a number of complexities (primarily the result of leap years) that I had to work around in order to get it to work correctly. Hopefully the text included in the calculation documents it well enough to provide you with an understanding of how it works.

    I also learnt that what I had been tought at school in regards to leap years was wrong, it isn’t quite as simple as every 4 years.


    I was comforted (and I also found it hilarious) that Microsoft appear not to have known this fact when building the 1900 system where 29/02/1900 is acknowledged as a date, although Microsoft blame IBM for this and their users need to move between Excel and IBMs Lotus 1-2-3 spreadsheet programme.

    Peace out.


Tableau Tip – Removing/adding sample workbooks

So this week I have no #Reviz project. It will be back next week but in the meantime I decided I would launch the Tableau tips section of my site, where I will post all manner of, well, Tableau tips.

I start off with one that I learnt today and Martijn Verstrepen, CTO of The Information Lab Netherlands, should take the credit for this.

It came from a question by Simona Loffredo on whether it was possible to change the ‘Sample Workbooks’ that appear when you open Tableau Desktop. Searches on the Tableau Community suggested not, but Martijn found the answer.

And now I have this.

sample workbooks.png

First question, why would we want to do this?

One example would be that if you are looking to develop the Tableau service within a company and wanted users to have sample workbooks of areas relevant to their business, or workbooks that showed best practices.

Another would be if you wanted a set of ‘template’ workbooks that users could quickly access, add their data source and begin building their work around a predetermined dashboard layout.

So how?

  1. Locate the location of your Tableau install, and navigate to the ‘Workbooks’ folder and then the country.C:\Program Files\Tableau\Tableau 9.3\help\Workbooks\en_GB
  2. Copy your desired packaged (.twbx) workbooks to this location. If you wish to add a thumbnail as well for the workbook, paste these here as well.
  3. Edit the ‘Samples.Manifest’ xml file in a text editor to include the details of your new workbooks.

    For each workbook your xml string should look like:


    Make sure too open your xml file with the following tag

    opening tag

    And close your xml file with this tag

    closing tag

    I have uploaded my samples.master file here so you can review the structure I have used. And below is an image of the files that I have strored in the location: C:\Program Files\Tableau\Tableau 9.3\help\Workbooks\en_GB


Once you have restarted Tableau you will now have your customised Tableau sample workbooks.

Customising the Tableau interface can really help engage new users and can massively help with retention and usability. I hope to include more blogs on this subject as I post more tips.


Disclaimer: This should be seen as a hack. Tableau do not support this process; and when upgrading to future versions of the software you will have to repeat the process.