Posts Tagged ‘line chart’

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 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 Advanced Date Filters

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…

Tableau Year on Year calculation

Tableau makes it so easy to do a year on year calculation. It’s a built in Quick Table Calculation. I’ll quickly run through how to do this. Set up a line chart with time (months is what I’ll be using in this example as it’s the most commonly used and easy to visualise) on the X axis and a metric, such as sales, on the Y axis. Drag the Year into the colour shelf so you have two years on the same graph. Read more…

Categories: Tableau Tags: , ,

Tableau hiding null values in line chart

You might notice that Tableau assigns null data values in a line chart with a value of 0 (zero) on the X axis. For example you’re trending this year and the previous years data for sales (or whatever metric) with the months on the X axis and the sales on the Y. If it’s mid year then future months won’t have any sales data as they haven’t yet happened. This default assigning of null values to 0 can be inaccurate depending on the report and at times it’s necessary to hide null values in Tableau.

This post is written in May 2011 and in the diagram you see below, using some dummy data, you see Tableau includes the future values, from June to December 2011, which haven’t yet happened, giving them a 0 value. Read more…

Categories: Tableau Tags: , ,