Tableau

data visualisation SQL Tableau 

How to create a calculated field in Tableau

admin 3056 Views 0 Comments , , , ,

Estimated reading time: 6 minutes

You are working on a data visualisation project, but in some instances the data may not be in a format that you want in the output. This is is where a calculated field comes in, it allows you define the output in a way you would like to see it.

So what are calculated fields exactly?

Tableau calculated fields can perform a number of different functions:

  • They enable aggregation.
  • You can use them to apply filters to your data.
  • If you have a need for ratios, they can be used for that.
  • Segmenting – return your data in specific segments.

In essence you can define how the output looks by using SQL or logical functions such as if, case, is null etc.

The purpose is that you can control and or define what is output other than what Tableau gives you , as a result the ability to include more detailed analysis based on your understanding of the underlying data is facilitated.

What are the different types of calculated fields?

There are three different types of fields as follows:

1.Basic expressions

Basic expressions allow you on a row basis, to get the data you want out of it. That could be for example finding a specific piece of data or finding the count of the existence of some data you want to analyse.

Here in our raw data , the source data contains 100K records, we have created a calculated field to count this:

Also we could just count the no of ‘Web’ occurences as follows, and assign a value of 1 to them. This utlisies an “IF STATEMENT”

2. Level of detail ( LOD) expressions

With this you can write expressions that give you more control and allow you to define the granularity you need to be returned.

In the below we have started out by year, but in the output it allows the data to be drilled down into:

As can be seen this code summaries up to the year, but in the bar, if you click the + sign beside “YEAR” then it starts to drill down into its lower level data . So the next level will be Quarter:

Then Month:

And finally day:

3.Table Calculations

In table calculations, Tableau allows you to create a new column whose output is a calculated value, usually completed by comparing one or more columns.

Some examples you may come across include:

  1. Month to month or qtr to qtr comparisons.
  2. Sales comparisons.
  3. No of new customers comparisons.

In Tableau there is some handy functionality built in which allows quick calculated fields to be shown on the screen.

As can be seen if you right mouse click on the shelf , of the data you are analysing, it will allow you to choose what type of table calculation to appear.

In the below we have chosen gender as the dimension to measure, and its percentage split. There is an equal split between male and female.

We could also, split it out by rank.

If you where looking for more functionality , then the following screens could also be used.

You simply Right mouse click on Sum(Qty) in the marks pane, and then edit table calculation.

with options:

and

So in summary there are a lot of different ways to create calculated fields, some through the functionality that Tableau has provided, others through writing your own logic.

Note and I have not shown it here, but if you are connected to a database, you can also connect using a custom SQL query. This functionality would only be available in the professional version.

data visualisation Tableau 

How to group your data in Tableau

admin 2786 Views 0 Comments , , ,

Estimated reading time: 3 minutes

Have you learnt how to connect to your data in Tableau and now want to understand how to group your Tableau data?

Here we go through a number of steps to help you understand better how to approach this, and what benefits it will bring to your data analytics project.

Why would I group in Tableau?

When you are working with large data sets , sometimes it easier to understand its meaning when the data is stored with simialr data items. Grouping the data has the following benefits:

(A) It allows a quick summary of data, and how large that data set is.

(B) Also groupings can alert to small subsets of data you may have not been aware of.

(C) Another benefit is that groups can be shown that have errors, and fixing them will put them in with the correct data.

(D) You can visually see groups, using Tableau will then you to keep them together.

Grouping by using a field in the data pane

The main way to group is when you are in the data pane, right click on the field you want to group by , then click create group.

For this example we can choose a number of values within channel, that we want to group by, here we pick all the items that have the value web.

You will notice that even before we click apply, it shows there are some data quality issues around the name that they are not consistent. You could use this to run metrics to catch these problems and count the no that occur.

When they are fixed then these should not appear anymore.

The output of this appears like this:

And on the screen , with the grouping now assigned, everything for Channel with web in it, is on one area:

Finally sometimes within your group, you may want an “other” category. The purpose of this is to catch items that dont fall into the group you have assigned, and sometimes they may come in later to the dataset as it expands.

You can achieve this as follows:

Giving in the output:

So in summary grouping can help you to identify a no of similar items to keep together, and also it is very useful to track data quality items as they arise and are fixed.

data visualisation Tableau 

How can I filter my data in Tableau?

admin 2458 Views 0 Comments , , , ,

Estimated reading time: 5 minutes

Tableau has some very powerful ways to manage your data through dimensions and measures.

Once you are happy how your data is structured, you will then need to filter your data. Here we talk you through the process and how you can use it to your advantage.

So why would I want to apply a filter?

This is probably the first question you should ask, and there are a umber of reasons:

(A) You have a large volume of data, you only want to see a section of it.

(B) You are building several versions of a report, a filter will allow you create those reports with the data specific to each.

(C) You maybe providing information within Tableau, but you may not want people to see certain information, so you just filter that out.

(D) Filters can also let you see problem data, which when found can be checked. In essence, you could use it as part of your data quality strategy within the organisation.

So how can I apply a filter?

There are a number of ways:

(A) On the data source tab within your project. This option is always available but sometimes forgotten about as other ways can be used, specifically on any sheet.

(B) When you are on your sheet tab, a filter can be applied as follows:

  • Drag a dimension or measure to the filter area:
  • If you have dragged a measure or dimension to the columns section, then just click the down arrow and then filter:

What are the types of filter I can apply?

The filter for dimensions is different to measures, below we will describe each.

Dimension filters:

  • General – This allows you to pick values that you want, one or a combination from the list is what is offered.
  • Wildcard allows you to put in values that are not in your list but can be searched for to see if any of the data contains them.
    • For example, on the below, we are asking the filter to only return any values in the Gender column that include “Fem”. So this would then exclude all the male values.
    • There are several different ways to approach this, as can be seen below.
  • Condition – Here they allow you to apply certain logic that must be met, based on the condition entered. This will only return values where the condition is met.
    • Note in dimensions, you only have the four options below
  • Top – Here we literally return the top no of records based on the conditions you apply.
  • Also you could complete by putting in a formula, using one of the other dimensions.

Measure filters:

Measure filters are quite intuitive as they allow you to define the parameters that the filters will be applied on.

As measures are numerical based, they will involve you telling the program what range or value to filter on.

Below are some examples:

data visualisation Tableau 

What are measures in Tableau?

admin 2385 Views 0 Comments , , ,

Estimated reading time: 3 minutes

Previously we discussed what are dimensions in Tableau. While they are important to understand, another vital part of Tableau data is measures, and they are equally important.

So how does Tableau define measures?

In their post on Tableau Measures, they define it as follows:

Measures contain numeric, quantitative values that you can measure. Measures can be aggregated. When you drag a measure into the view, Tableau applies an aggregation to that measure (by default).

Source: Tableau

So in other words, it is a set of information that allows you to compute statistics on the data e.g. sum , count, average.

When your data is loaded into the Tableau project, it automatically deines which are dimensions and which are measures.

As a result you don’t need to do this step, but they can be changed, and this is described below.

Where will I see measures on the Tableau screen?

Normally they can be seen on the left hand side on your sheet like below, directly below dimensions:

Where can I incorporate measures in my Tableau project?

They can be incorporated as a column or row value. Also they can be included as a mark or as a filter as well.

Where can I find the default properties of measures?

In each measure value there are menus associated with each, and you can change any property by clicking on the down arrow.

In the below screenshot, it can be seen that for QTY, in the drop down menu , there are a number of options to change in the default properties section.

Can a measure be changed to a dimension?

The answer is yes, but read on.

If you want to convert a measure to a dimension, first of all you got to ask , why you would want do that?

Some values, may be classified as measures, but in actual fact they maybe better suited to dimensions.

  • An example would be a postal code, while it contains number and letters, it might not always be suited as a measure.
  • Age is another example, where you might not want to aggregate, for example, sum or count? In this instance you may want to put them into buckets of age categories, and this is where changing them to a dimension may help.

If you want to change a measure to a dimension, some of the options are:

(A) Drag the measure to the dimension area on the sheet tab.

(B) Right mouse click on the measure, and click “convert to dimension”

We hope you have enjoyed this post, you can see more Tableau posts here.

data visualisation Tableau 

What are dimensions in Tableau?

admin 2653 Views 0 Comments , , , , , , ,

Estimated reading time: 6 minutes

In our previous post, we showed you how to connect to your data in Tableau. Now that you are in we are going to discuss dimensions and what they can be used for.

What are Dimensions?

When you login to Tableau, you will see the dimensions information on the left hand side as below. We have included the below from our previous post.

When we talk about dimensions, they are values that contain information that can be measured. So, in other words, you can aggregate them into groups.

Aggregating essentially means you can get summary information on the dimension.

Typical examples of aggregation that could be applied include:

  • Count
  • Sum
  • Minimum
  • Maximum

How do you use dimensions in Tableau?

What you can do is drag the dimension over to the column or row values, as per the below:

OR

You can then drop additional information into the sheet , to give you more enriched data to analyse. Below we have “Country” in columns and “ISO Code” in rows.

As you will see above there is no actual information that you can use, to make a decision on. The raw file we have pulled this information from which has 100K records in it, can have a count applied against the “ISO code” row field as follows:

As a result we can now get a better view of the data as follows:

What are the types of things I can with a dimension?

You can change the characteristics of dimensions to get them in a format you would like. Some of the possible ways you could achieve this is:

(A) Create a calculated field, this allows you to have additional information that assists better decision making.

(B) You can change the data type of the field. I would check before doing this as it could have a profound effect on how the data is presented, and not every field may be suited to doing this.

Changing the field ” country” from string to boolean data type as an example, yields the below, which is not very meaningful.

(C) You can change the default properties of the what is presented to you:

In this scenario, I have sorted “country” ascending, and in alphabetic order

outputting the following:

(D) Another option if you so desire is to change the Alias on a field, to a name that is more suitable or meets your needs better.

This can be achieved as follows:

then

As can been seen from the above, I originally had the first bar as “Australia”, but then changed its alias to AUS.

As a result the chart updated to shows AUS.

In this scenario changing aliases , you would need to be careful as some names are quite similar, i.e. Australia and Austria

Luckily Tableau has that sorted, as if you do try to do that, you get the following:

You can also see other tableau tips we have written!

data visualisation Tableau 

How to connect to your data in Tableau

admin 2829 Views 0 Comments , , , ,

Right you have made the decision to utilise the benefits of data visualisation , to present your data in an easy to follow format.

Tableau is one of the top industry tools that allows you to manage your data, and present it in a fashion that enables decision making.

What are the ways to connect to your data in Tableau?

There are two possible ways to connect as follows:

(A) Accessing your data in a file

Below is a list of the possible file types. As can be seen Tableau accommodates not just files associated with their software, but also files commonly used by people and organisations in their day to day work.

The most common way to quickly access the files is through the menu on the side:

or through the file option at the top:

(B) Accessing a server by logging into it:

As can be seen above, the power of Tableau is that it enables the connection to a multitude of industry leading server technologies.

As long as you have the right credentials, and the server side security is setup correctly this should work seamlessly.

Connecting to a file

To start off one of the first things to check is your file format. We are going to import a XLSX workbook, with data on two tabs:

When Imported the screen will look like this:

On the left hand side the the sheets in table formats, in this example “data” and “lookup”, these are the two tabs in the source file:

All you need to do is drag one of them over to the right hand side upper panel. Tableau automatically will then load the data in , the bottom panel for you.

You should note that Tableau automatically assigns data types to each column.

Connecting to a server

As of the writing of this article, I have no server access, but below are a sample of some interfaces and data required for some of the server types:

(A) Cloudera Hadoop

(B) Google Cloud SQL

(C) Impala

(D) Microsoft SQL server

(E) Oracle

(F) Snowflake

(G) Teradata

What happens if I update my source data?

The great thing about Tableau is that , when you load in your data, you can save the project , and the connection to it within a file.

When you leave the connection to the source file is saved, and as long as the source file is not renamed or deleted, the data will always reload.

So in the raw file we have used in this example, record no 1 has a customer id of 2322.

Existing raw file in excel:

As I have the Table project open and the raw file connected, it will not allow me to change it.

Now I am going to change the customer id value to 2345 in the excel file, save and close.

When I reopen the Tableau project, as it has already saved the connection to the excel file, it reloads it, but with updated and refreshed data.

Now it can be seen that a refreshed copy of the data is in Tableau. This is very useful as all that needs to happen is a refresh of the data, and the user of the Tableau data visualisation has fresh information to work with.

Next step, interested to learn what are dimensions in Tableau?