Create a custom multiple values filter in Tableau with a Parameter Action

Standard

Tableau provides a bunch of different filtering options to enable the functionality we need to interact with our data. However, while practical, there are very few options to customise the look of these filters. After spending a long time polishing that perfect dashboard, you want your filters to blend in seamlessly.

A screenshot of a Tableau dashboard, showing the different options for configuring regular filters
The different filter presentation options available on a Tableau dashboard

Instead of heading to the limited formatting options for your filter controls you can create your own filter interactions by using sheets, which give you all the customisation options normally available when building visualisations.

Examples of customised filters include creating legends that allow you to filter your visualisation or filtering your view using shapes. However, in both these examples multiple items can only be selected if the user holds down the shift button. That’s not a very intuitive or user friendly way to interact with a filter.

Set actions allow us to extend these examples, by allowing us to add items to a set incrementally, without having to use our keyboard to select multiple items. But what if you want to both select and then deselect items again to flexibly change what is in your view, just like with a regular multiple values filter? A set action can either add or remove from the set, not both. I want to show one way that a customised multiple select filter can be created in Tableau, using parameter actions.

The goal

  1. I have a list of the items that can be filtered
  2. If I click an item it is added
  3. If I click it again it is removed
  4. It is clear which items are selected and which are not
  5. The clicked item does not highlight, only the symbol changes
The final dashboard

How to do it

How the filter sheet is set up
  1. Create a sheet that will be your filter sheet
  2. Drag the field you want to filter with to the sheet. I am using the Tableau “World Indicators” data for this example and am setting up a filter for region so I placed this to the rows shelf.
  3. I want my filter to also act as a legend, so I place Region on colour.
  4. I don’t have a need for tooltips in my filter, so I turned those off.
  5. Now create a parameter. Set the data type to string and allow all values
How to set up the parameter
  1. Create a calculation that will use this parameter.
    1. The calculation evaluates if the field that is being selected is part of the current parameter value or not. The parameter is a concatenated list of all the values that are selected.
    2. If the parameter already includes the selected value then it removes it from the concatenated list by replacing the value with .
    3. If it is not then it adds the value to the list of all selected values in the parameter.
  2. Underscores are included to delineate the individual values. This will need to be customised to what the values are that you are stringing together. If your values already include underscores replace this part with a symbol that isn’t part of the field.
  3. Place this new calculation on the details shelf of your filtering sheet. In my example this is called “Add/remove region from parameter” 
IF CONTAINS([Parameter], '_' + [Region] + '_')
THEN REPLACE([Parameter], '_' + [Region] + '_', '')
ELSE [Parameter] + '_' + [Region] + '_'
END
  1. From this field you create another field that checks if the value is in the parameter list or not. For this you can just take the first statement of the IF syntax, which will evaluate as a boolean. I called this field “Is region in parameter?”
  2. This is the field that I use to distinguish the selected and not selected regions. I placed it on the shapes field and assigned two different shapes to the TRUE and FALSE values so that a filled square is shown when a region is selected and an empty one when it is not in the parameter.
 CONTAINS([Parameter], '_' + [Region] + '_')
  1. As we are setting up a filter we of course need something to be filtered. I created a simple line chart for this example and placed the second calculation “Is region in parameter” onto to the filter shelf of this sheet to show only the TRUE values.
  2. The next step is to create a dashboard and place both sheets on this.
  3. Now create a dashboard action where you select the filter sheet as the source. On select, the parameter that I have created is changed, based on the first calculation that I created. We don’t want anything to happen when we clear the selection, so it is set to just keep the same value.
How to set up the parameter action
  1. With this our filter already works, but whenever we click a region to add or remove it from the line chart the selection gets highlighted. There are a few ways to stop this from happening but I followed the approach explained in this video.
  2. I created a “No highlight” field, which just contains a string with those words, placed it on the details shelf on my filter sheet and then set up a filter action to filter from that field to one I don’t use in my view.
A filter action to stop selections from highlighting

With all these steps completed I now have a filter set up that allows me to add and remove regions from my line chart, with just three calculations, one parameter and two dashboard actions. The benefit of this approach is that it works with fields that contain many different members, as we don’t have to create a calculated field or action for each individual item.

You can test out the custom filter and download the workbook on Tableau Public.

Do you need to be a SQL expert as a Tableau developer?

Standard

I’ve looked at a lot of job adverts aimed at Tableau Experts since finishing The Data School and many of them want you to be a SQL expert. I had placements with four different companies, of which one was a consultancy, a two-year job and several freelance projects as an analyst before I actually needed “advanced” SQL skills.

So is it worth learning SQL if you know Tableau and Alteryx? What does it mean to be a SQL expert? Do the jobs that ask for SQL experts really need them? And how do you get a job that asks for SQL experts when you aren’t one?

Do they really need SQL experts?

Not every job advert that asks for SQL experts actually needs one. I believe this is due to several factors. The first being that there is an assumption that analysts started out using SQL and Excel before they moved on to more specialised tools such as Tableau and Alteryx. For analysts that got a lot of their training on the job this might be true, but coming from a trainee programme myself, which was specialised in Tableau and Alteryx, my path looked a bit different. There simply was no need to work in Excel, and we covered SQL with just one week of training but never used it. So when HR is putting together that job description, they might feel that by adding SQL as a skill they are making sure that they get the “real analysts”.

My second theory about why SQL experts are often sought for Tableau roles is that if an organisation is new to Tableau, they haven’t quite understood yet what Tableau can do. I have seen analysts that came from a SQL background to Tableau, who were quite proficient with Tableau, still use SQL to answer ad-hoc questions. Going into Tableau to explore the data for an answer is still a different way of thinking, and doing this in SQL, with repeatedly writing out queries, is what they are used to and good at. If they are the people feeding into the job advert, then it is natural that they will believe their new team member will need the same skills, because they might not understand that you can use Tableau for the same purpose.

Why SQL might be needed

But that doesn’t mean they are all wrong. There will be many roles that are asking for SQL skills because the role really does need them.

The main reason why you might need SQL is automation. If you have a database, and you are connecting to this with Tableau, there might be times when the data setup required becomes too complex for Tableau. You can achieve better performance or a quicker workbook setup by forming the data into a more suitable shape. Even if your organisation uses Alteryx or another ETL tool, you will need a way to automate this, which in many cases adds significant cost and is thus not available. The quickest and cheapest way to reshape your data and support Tableau updates at the speed of your database updates is with a custom SQL query or view.

Secondly, if your team mates are all SQL experts then this is the way they will communicate with each other. You want to be able to QA their work at some point and understand what they are saying when they post queries into Slack, highlighting oddities they have discovered in the data.

Moreover, even if you are not responsible for creating tables in the database, you need to understand them, and detailed documentation is often not in place. Instead, the team might simply point you in the direction of the source query that creates the table and you will need to figure things out from there.

In my most recent role there was another reason, which is that we often didn’t provide analyses, we provided data. I could have created a Tableau workbook connected to the same data, with the same conditions and then exported the data from there. In the end Tableau is just an interface to create a data query. But this would have required my teammates to understand and check what I had set up in Tableau, which is actually more complex than interpreting someone else’s SQL query. You need to click into different calculations to see how they are set up and make sure you check if there are any workbook level filters applied. With a SQL query it’s all there in one view, which makes it easier to understand the logic behind an analysis.

Finally, it is easier and more space efficient for documentation purposes to save a SQL query in a simple txt file rather than a Tableau workbook.

Ok, so what now?

Having the very basics of SQL skills is always useful as an analyst. If you are working with databases, it is good to have a little understanding of how they are set up and how you would directly interact with one if you really had to. In The Data School we did this training over the course of one week, and I’d say once you know your way around the basic structure of making a query, grouping it and adding some filters, this requirement is met. There, you can put SQL on your CV.

The next step is to determine what the job advert actually means when it asks for “expert SQL skills” and why the organisation wants them.

If you make it to the interview stage, find out some of the following:

1. What does the team use SQL for?

They might use SQL to create functions in the database, that you need to understand but not be able to create yourself. Equally, at this point the interviewers might say that the team uses SQL to analyse data and find answers. You can then explain to them how you do this with Tableau and the benefits this has for you.

2. What is SQL used for in relation to Tableau?

Perhaps they don’t do this at all, and SQL is just used to set up the database. Perhaps they constantly write custom SQL because they don’t know how to manipulate data in Tableau and this approach is easier for them. Perhaps they do this because the complex data structures mean that they need to prepare the data into the right shape because Tableau wouldn’t be able to handle it.

Try to assess if the use case is something you can circumvent with your Tableau knowledge or if what they are describing sounds like you would need to use it the same way.

3. What SQL knowledge do they believe you should have?

SQL can be used to manage a database, by creating users and schemas and assigning roles. While rare, this might be all you need to do. SQL is also used to create data structures and to retrieve them. The knowledge needed for these different use cases is quite different, and you can be an expert in one area but known nothing about another.

Ask the interviewers to describe what they mean with those “expert SQL skills” that they are asking for. Can they give examples of commands you should know, for instance:

  • A lot of requests we get is for pivoted data, you should be confident in reshaping data
  • The database is quite clean, so all you need to do is retrieve data and apply the relevant conditions to it
  • The database is still work in progress and part of your role will be to create complex calculations in SQL to create reusable functions and materialised tables
  • You will frequently need to use RegEx with this data
  • We work at a fast pace, so it’s important that you are very quick at writing SQL commands
  • There is a need to combine data from lots of different tables, so the queries can be very long and complex

Tell them what you know

Their response might still not be 100% fitting to what you will actually need to do in this role, but it gives you a chance to evaluate if you think the role is suitable for you. Equally, if they can’t really answer these questions, it might be an indication that SQL skills made their way onto the job advert somehow but aren’t actually needed.

The answers you get to these questions might convince you that you don’t really want this job, and that you would rather work in a place where other tools are used. That’s absolutely fine. An interview is a two-way process.

If you do want the role, be honest about what your skills and experience with SQL are and if you believe you already know how to do the things they have just outlined or where you think you would need to learn more. The thing is, if your skills in other areas are convincing enough, they might be perfectly happy with giving you some time and support to learn on the job. Explain to them that you are willing to learn, and ideally give some examples of other things you learned, how you taught yourself and the other skills you have that will make this easier for you.

Recently, I was offered two jobs that listed advanced SQL skills as one of the requirements. I was honest with both of them about my limited SQL knowledge, and they were willing to support my learning.

Learn HTML and CSS

Standard

I first learned a little HTML when I was a teenager, but never got very far with it. More recently, I have been in touch with it when scraping websites and my work with data visualisation has made me more interested in web development and UX/UI design. So a few months ago I decided to teach myself the basics of HTML and CSS. I managed to get to the point I wanted to within about four weeks, while working full-time and doing all of it for free.

My approach

  1. I started out with the one-week free trial you get as a new user on Codeacademy and made my way through as much as I could from the course “Learn how to build websites“. Rather than doing all of the lessons strictly in order I was selective about which to skip, and concentrated on the content delivery lessons in the later modules rather than the practice, as I knew I would be able to do this on my own later. This process got me to the point of understanding the basic structure of HTML and CSS. (If you are happy to pay a little money for a monthly subscription you can complete this course and take more time for this step. The remaining steps of this list are still relevant.)
  1. I then used Pinterest to select some websites that I tried to copy. The aim of this was to practice what I had learned and identify gaps in my knowledge to figure out what to learn next. Because I knew the basics of HTML and CSS structure, I was able to just look up any particular commands I had forgotten or not learned yet.
  2. One part of the Codeacademy course that I did not have much time to engage with was layout, so all I could do was arrange items vertically on the page. I spent about another week just concentrating on learning Flexbox, which allows you to control exactly where you want objects to appear in relation to each other and how you want them to react when the site is viewed on different-sized screens. I highly recommend Flexbox Zombies for this, as it explains the concepts very well and includes endless repetition, which really helps to understand and remember all the different commands.
  3. Besides just learning website coding, I also wanted to learn more about the visual design aspect. I got started with Figma, a very intuitive, free, programme made especially for the purpose of designing websites and apps. Watching this one-hour video on Youtube was pretty much enough to get me to the point where I could use the software and could then get by with just looking up the occasional functionality.
  4. I selected an existing website that I felt could use some improvement, and used Figma to design a makeover. This is much easier than starting out with a completely blank canvas. It also has the benefit that all images and copy are already available and that the website layout is most likely quite simple.
  1. There is a follow-up video to the one mentioned in step 4, which takes the Figma design and translates it into HTML and CSS to create the complete website within an hour. This is really helpful, as it shows all steps from start to finish and includes for instance the approach the developer is taking to reusing code and the order in which he approaches the build. This sort of practical information doesn’t really get covered in any of the tutorials or courses that I tried and is useful to tie all the pieces together.
  2. With all this knowledge I took my Figma redesign of the existing website and started coding it. As the design had multiple pages, this took quite a while, and as I was going along, it allowed me to practice what I had learned and look up new things that I had done with ease when creating the design in Figma but now had to translate into code. It gave me a good insight into what it would be like to create a full website rather than just code snippets.

Where I am now

My focus shifted from this project to strengthening my SQL skills and learning Python, so my progress has halted since step 7. Currently, after going through the steps above, I feel that I am at the stage where I can create a simple website and understand how other sites are set up. Some things that my new knowledge has helped me do:

  • Understand and change how Tableau visualisations are embedded into other websites.
  • Better understand other code, such as Python.
  • Have a better understanding of how websites I want to scrape are structured and thus how to take them apart most effectively.
  • I used to create any images or icons I needed for Tableau in PowerPoint but now generally use Figma.

Next steps

While I am not concentrating on this path at the moment, I will definitely pick it up again in the future. There are several things that I want to concentrate on when I do, which weren’t covered sufficiently in the steps I took:

  • I still struggle with optimising a site for different screen-sizes or devices, which is of course really important nowadays, as so much content is consumed via mobile devices. I will seek out some dedicated resources on this subject.
  • I recently tried to create a design for my consulting website in Figma. Even though I engaged with lots of different resources on webdesign and I generally think of myself as having decent design skills, I found it very challenging to come up with something that looked professional and modern. The final website was set up by my partner, who is a developer, and we used templates to make the build easier and quicker. I now follow a lot of UI design on Pinterest and Instagram to get a better sense of how to create appealing interfaces, and will continue to practice, especially as this is a skill that is also very useful for Tableau.
  • I now have a live website and my own domain, but have very little understanding of how this was set up and what I would need to do if I wanted to host another site. While I know how to locally code a website, I still need to learn how to share this with the rest of the internet.
  • For additional functionality, many sites use JavaScript in addition to HTML and CSS and this is a language I would love to add to my repertoire. Especially, as D3 is a JavaScript library and would be incredibly useful as an alternative to Tableau for embedding visualisations on websites. Additionally, JavaScript is also used to build Tableau extensions and web data connectors.

Calculation testing in Tableau – A beginners guide

Standard

A while back I wrote about some steps you can take in order to systematically familiarise yourself with your data. Here I want to share some advice on similar approaches when creating calculations.

Creating calculations in Tableau can sometimes feel daunting when you first learn Tableau or don’t consider yourself a “data person” or a “numbers person”. Part of this is down to the invisibility of Tableau calculations. They happen somewhere in the background, off screen, and it can be difficult to tell if they are doing what you want.

If you have created a valid calculation, but it isn’t giving you the results you expect, or you need to check that it is correct, try the following two approaches.

Making calculations visible

The best way to create your calculations is to watch what’s happening by creating a simple table that includes the variables that you are using. Create your calculation, drag it into the table and see if it matches up with what the result should be. If you make a change to your calculation you can simply select “Apply” to update the values in the view.

I find this helps especially with Level of Detail calculations, as you can set up the table to show the level of detail you are trying to achieve, then create your calculation and add or remove dimensions as needed to test if it still maintains the same values.

This calculation extracts the maximum of either “CC_last_active” or “Last Login Date” depending on which is higher, accounting for possible NULL values. Having all three fields side-by-side it is easy to compare the results visually to see if the calculation works as expected.

Breaking down calculations

If you have a calculation that consists of multiple parts that just doesn’t seem to work the way you want it to a good way is to break it down. I tend to do this by cancelling out different parts of the calculation with the double slash or taking out one part of the calculation and pasting it into a new calculation, to check that it is doing what I expect it to.

Create your checking table as explained above, then make sure that each part of your calculation is working as it should be in isolation. Add them together bit by bit to find out at which point things are going wrong. Comments are also really useful to just leave yourself hints about what each part of your calculation is doing and are vital for documentation if anyone should take over to maintain this workbook in the future.

You might also choose to break down your calculation into various individual calculations and give them names that make sense to you. In my example, I could have created four calculations instead of just one.

The first, second and third condition of the IF statement could each have been a separate calculation, which I could have tested separately and then referenced in a final calculation.
  1. Last activity for CommCare users
  2. Last activity for Tableau users
  3. Last activity for cross-platform users
  4. Combine all three

This is a matter of preference, as individual calculations can seem less complex in this way but it requires you to switch between various calculations when you are making changes.

Confidence in Calculations

The two methods above helps me have confidence in my calculations and help me figure out more complex processes. It is always good practice to make sure that your calculations are doing what you expect them to, and to investigate the causes if they don’t.

What are your calculation checking best practices?