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…
I’ve had some questions recently about hiding Nulls in a filter in Tableau. This is quite straightforward and the same technique can be used to hide any value from a filter.
NOTE – if using this technique it actually removed what you’ve hidden from the filter from the worksheet.
All you need to do is duplicate the field being used as the filter, put the duplicate field on to the filter shelf, select the values you want to hide – i.e. Null – and click exclude on the right of the filter box. Read more…
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…
It’s possible using a combination of parameters, filters and calculated fields to create complex and advanced date filters in Tableau.
Say you want to see some a time period based upon a user selected time period. You can create a calculated field which will return a result based on what the user selected.
For this example you want the user to select a month, but you want to display results showing data from the month before the user selected month as well the selected month – i.e. the user selects Apr and you also need to display Mar as displayed in the visualisation.
This wouldn’t work with a standard filter, the user would need to select the months they were interested in one by one. Using a parameter and a calculated field is the way around this. Read more…
The introduction of parameters with Tableau 6 have made this software far more powerful. Intelligent use of these have made many things possible that we couldn’t do in previous versions including allowing the user to dynamically select which measures or dimensions they would like to see on the visualisation.
This could massively reduce the number of worksheets required. Where previously you would have needed to create 2 charts to show 2 different metrics now this is possible on one chart where the user can select what measure they want to see. Read more…