Learn Tableau Basic Calculations (Calculated Fields) in Tableau (with 10+ examples) | Part 1

if you're new to tableau and want to learn about calculations or if you've been using tableau for a while and just want a refresher this video is for you we're going to cover what calculations are where to find them how to create them different types of calculations and we're going to go through a whole series of examples for some kinds of [Music] calculations [Music] so what are calculations calculations are a way to extend your data source in the perfect world we don't need calculations in the perfect world we have all the data points we need in our data source but we know that doesn't exist our data sources are often incomplete they're messy and sometimes we just need to derive some data points from logic business rules or maybe additional data points that already exist in our data source enter calculations calculations allow us to do that calculations allow us to have that one more column that we wish we have in our data source tableau has three main types of calculations basic calculations level of detail expressions or lod expressions and table calculations in this video we are going to focus on the first one which is the basic calculations this is further broken down into row level calculations and aggregate calculations just as a reminder i am using a fictional database for my data source this is based off of the chinook database which is a sample database made available for several relational database management systems so how do you spot calculated fields in tableau in your sidebar you are going to find a visual cue that allows you to identify the calculated fields easily it will start with an equal sign it means that this field is made up of some logic or expression that helps derive its value so what makes up a calculated field a calculated field can have many different components so let's have a quick look a calculated field will have a name it can optionally have comments which is documentation that you're going to embed with your calculated fields so that you'll remember the logic the reason or anything else that anybody needs to know about that calculation it can have field names so you can simply drag over field names in here it can have what we call string literals so if there is a piece of text that you want to embed into your calculation you can also do that it can have operators so in this case we're using an operator called a string concatenation operator which is a plus sign and you can have many other kinds of operators it can use parameters or you can also use functions there are many functions that tableau supports that you can embed inside your calculated fields functions are reusable code and a lot of them are built into tableau so you can just use them whenever you need them so how do we create calculated fields in tableau there's many different places where you can create calculated fields the first one is this drop down arrow right underneath your data source window you can create a calculated field from here and what it does is it opens up this calculated field editor where you can type in your calculated field name you can type in your expression and there's also this little arrow that you can expand that gives you a list of all the functions that are available in tableau so this is a great resource especially if you're just starting out because you can kind of go through the different groups of functions and you can also take a look at the syntax or the way you're supposed to use it or write it it gives you a description and it also gives you examples one word of caution though some of these functions may be similar to functions that are supported in other tools that you already use so be very cautious that this is still a tableau language for example i use a function called datedif in sql server in sql server it has a slightly different syntax and the values that i need to pass in are also slightly different as far as tableau is concerned this is the syntax so i have to pass in what's called a date part a start date and end date and any time you see square brackets in your syntax it means those are optional components you don't have to pass them in here tableau also provides a description and also an example of that function and this is where it's a little bit different in sql server i pass in a different value but as far as tableau is concerned when i'm passing in the date values i'm not passing them as string literals so i'm not using double quotes around these dates i am using the pound sign again just as a word of caution just have a look at the documentation just make sure you know and are aware of how to use the data what the syntax is and how it might be different from other functions that you're already familiar with a second way to create a calculated field is simply right clicking on a field in your sidebar create calculated field and what this does is it pre-populates this calculated field editor a third way to create a calculated field go to analysis create calculated field a fourth way to create a calculated field is simply by double-clicking on an empty area in your columns in your rows or in your marks card notice that this becomes almost like a text box you can start typing things in here and this is what you call your ad hoc calculations one limitation of ad hoc calculations is you don't get that function list so when you use this you should be aware of what formula you want to enter note that this ad hoc calculation exists only in the current worksheet should you need this for other sheets or other graphs in your workbook simply drag this over to the sidebar and you can start using it in other worksheets let's now move on to the first basic calculation which is a row level calculation a row level calculation means that a value is going to be derived for every row or for every line that you can find from your data source let's say you have a set of email addresses and what you simply wanted to do was to extract out the email domain you can start creating a calculated field and typing in your formula or we can also use a built-in function in tableau called transform when we right click on this field called email there is an option called transform and what this does is it allows us to split up our string the difference between split and custom split really depends on what we call a delimiter or a separator if you have a non-standard separator in this case we're going to use an add sign and we simply wanted to extract portions of it we can use the custom split so clicking on custom split in here we can specify we want the separator to be the add sign and we want to just take out the last column after the add sign tableau creates a calculated field for us we can see it in this pill with an equal sign and just to validate let's drag this over and we can see that it has successfully split up all of the domains out of the email address what if we had a first name a last name and we needed to have a full name let's create a calculated field on the drop down create calculated field let's give this a name we can start typing in our field name and we're going to see that there's a drop down that gives us an autocomplete so we can choose from here or we can simply just drag from the sidebar any of the fields that we want to be part of this calculated field the calculated field editor is a modeless window what that means is i can keep this editor open while i'm still working in the background so in this case i'm going to leave this editor open and i want to get the full name and display it just to see if i actually did get it right so in this case we can see that we have our first name a space and our last name let's create one more variation of name just to be able to showcase some of the functions available for strings so in here let's create another calculated field we're going to call this customer name and perhaps what we need is just the first name initial so we have a function called left we can pass in the first name get the first character from the first name we add a dot and a space and now we can also for example use uppercase for our last name we are going to keep this window open and we're just going to see what the result looks like so in here what we do have is just the first initial followed by a dot followed by a space and then we converted our last name into all uppercase what if you wanted to simply check the existence of a keyword or a text that may be embedded in another text field tableau has string functions that will allow us to do this for example there are functions like contains ends with starts with there's quite a few other ones that are useful so please explore this list of string functions in this example perhaps we just want to tag all of those tracks where u2 or bono are composers so we can use contains we can pass in the composer field and we are looking for example for youtube in here we can have multiple conditions together if we're looking for an either or we can use the or keyword let's copy and paste the previous one in this case maybe we're just looking for bono now there are other operators you can use an end as well if both of these conditions have to be true but right now let's click on apply and let's see so for the ones where it's true can we check contains pono contains bono contains u2 so in here we know it's true but if we change our operator to an end it means that both of these have to be in the composer list let's say that we want to identify each of our line items as either profitable or not there's a couple ways we can do this we can create a calculated field that simply returns either a true or false so in this case if we create a calculated field and we are looking at each individual line item we can identify whether profit is greater than zero or not and if it's greater than 0 then it means it's profitable once we click on apply we're going to see this new field that has a t or f right beside it which means it only gives us either a true or false so let's drop that to our view if the profit is negative then it's not profitable if the profit is positive then it is profitable but a lot of times we want to make this more readable so usually you are going to enclose this condition in some kind of an if else statement also notice in here that if your syntax is incorrect or if something is missing tableau will give you an error at the end and it will give you hints as to how to correct your syntax so that your calculated field will work so in this case the if statement must have a corresponding then and it must be ended with the keyword end so if the profit is greater than zero then we may want to mark it as profitable else maybe we just want to tag it as not profitable and we have to remember to enclose the if statement with an end clause so the only change that happened in here is instead of true or false now this is something that's a little bit more readable what if in your data source you only have pieces of a date you have the year you have the month you have the day how can you compose a date out of this in tableau the good thing is it's actually pretty straightforward and quite easy to do in tableau there is a function called make date and all you need to do is to provide the year comma a month comma the day in numeric format and it will create an actual date data type for you so let's call this an invoice date invoice date let's type the function in and remember that when you're creating your calculations in tableau you can either use the autocomplete so maybe in here we're going to type in invoice here or you could also simply drag it from your sidebar invoice month and then invoice day so let's drag this over just to double check if we did get it right right click drag display the invoice date in discrete format and right now again let's just double check it is crucial that you always double check the results of your calculations just to make sure just to get that level of confidence that you got it right so in this case if the month is 1 the day is one and the year is 2015 it looks like we did get it right but what if what is coming to you from your data source is an unconventional date format how should we do this how should we deal with this in tableau again tableau makes it very easy for us to work with dates there is another function called date parse the date parse function does allow us to parse out or extract out the pieces of a date so that we can compose an actual date field so for example if we have the day of the week in the string that's coming in we can parse this out using a symbol e so if it's a shortened weekday we're going to pass in three e's if it's the full week day name then we're going to pass in four e's so in this case let's parse out this unconventional date and create an actual date field from it so it looks like we have a weekday name we have a dot we have the full month name we have a dash we have the year and then no space in between you have the day of the month to create this in tableau let's create a calculated field let's call this invoice date 2 and this is going to be date parse the format will be four e's for the full weekday name followed by a dot followed by four capital m's for the full month name we have a dash four y's for the four digit year and two small d's for the day of the month comma and then we're gonna pass in the actual string or the field that contains this unconventional date value moment of truth let's see if we got it right let's right click drag invoice date over display the discrete date this is april 29 2016 and this is actually what we are parsing out we often have to do date calculations in tableau one common question is how long ago something has happened there is a function in tableau called datedif and this datedif function will accept what's called a date part you have the start date you have the end date and optionally you can pass in the start of the week in case you're wanting to know the number of the weeks that have passed the date part values specify the interval that we want to use in our calculation this can be used in many other date functions but note that these are predefined string values in tableau you're going to have to type this in as is this is case sensitive otherwise you are going to get some syntax error so if we wanted to know how long ago the invoice has been we can use the date div function pass in the unit and in this case it's going to be day the start date will be the invoice date and we can also embed functions within functions in this case tableau does support a function that retrieves the current day this calculated field produces a number and you're going to find this new field under the measures section let's now move on to the second type of basic calculations which is aggregate calculations and what it is is really taking a group of records and generating one result out of it so for example we may want to take this group of records for a particular city in canada and generating either the number of records the average of unit profit the total unit profit the minimum profit maximum profit aggregate functions will generate one result for the group that you specify when you first start working with tableau and start dragging over any of the measures you may not realize it but you're actually already doing aggregate calculations in this case i drag profit over but what gets displayed is not the individual profit values but it's the sum of profit values and we can see from here from the pill it actually is an aggregation it's the sum of profit the way tableau works is all of the measures are going to be automatically grouped by any of the discrete dimensions in this case profit is going to be subdivided or grouped by country so for example for argentina if we take a look at the underlying data we're going to see that the total profit in the summary is 40 but when we take a look at the full data it is actually composed of 38 rows so 40 dollars is the cumulative total of the 38 rows in addition to just dragging and dropping the measures we can also create our own calculated fields so for example if we wanted to know the number of customers the number of unique customers there is a function called count distinct and in here we're going to pass in our customer id if you are curious to see the other aggregate functions simply click on this drop down go to aggregate and these are all the functions that tableau supports for aggregation so let's drag customers over and there you go notice in here though that if your calculated field already uses some kind of aggregation when you bring that pill over it's actually going to have the agg around it so agg simply means that you can't change the aggregation anymore the aggregation is defined by whatever you have in your formula so just to compare these two profit was brought over as a single value so we have profit on the left-hand side in your sidebar when we bring this over you can still change the aggregation so on the drop-down you can adjust this it can be an average a median account but for number of customers this option is not available anymore there is no measure in here the aggregation is defined by what you type in into that calculated field if you need to you can also embed additional expressions in your aggregate functions for example if we simply wanted to have a calculated field for profitable customers let's type in the name and in our calculated field in our aggregate function we can technically embed an if statement should you need to do something like this so if profit is greater than zero we may want to display that customer id or we may want to to count that customer id if you do not have an else statement in your if it means it returns a value of null or a missing value in this case if profit is greater than 0 this will return customer id which will eventually be counted by this aggregate if the profit is 0 or if it's negative this simply returns a null value which means it doesn't get counted the order of operations matter in calculations especially when we are calculating ratios a profit ratio is a metric that allows us to identify if a business can generate money for this demonstration i'm going to create two calculated fields one will be a row level calculation the other one will be an aggregate calculation so for example let's create a profit ratio and this one will be the row level let's take profit divided by the sales amount let's add that and i'm just going to change the default properties so it shows up as percentage let's create another calculation this time it's going to be an aggregate let's call this a profit ratio aggregate this time around i'm not simply taking profit i'm going to take sum of profit divided by the sum of the sales amount we're going to change the default properties for this one as well to show percentage and let's display both we have row level and we have aggregate let's move these around so it's easy to compare right now it looks like the values are exactly the same so does it really matter if we have used a row level calculation or if we've used an aggregate calculation notice though that i'm showing you right now the most detailed level each line here corresponds to a line in the data source right now it doesn't matter because they're at the most granular level but notice what happens once we start rolling up our numbers so for example if i take away the invoice line id it means everything gets rolled up to the invoice id level the numbers start changing and the numbers don't seem correct so for example a profit of 3 cents with a sales amount of 12 dollars and 10 cents the profit ratio at the row level is minus 17 the profit ratio at the aggregate level which is the correct number is 0.26 so what happened let's undo this and this time around i'm going to add our totals i'm going to go to the analytics tab i'm going to double click on totals and it will give me all of the totals and subtotals for this text table for the row level calculation what's happening is it actually does a division first generates a result and by default once you drag that over to the canvas it's going to be aggregated and the aggregation is sum so the minus 61 has been added to the 44 thus the result is minus 17 and we know this is incorrect with the aggregate calculation the summing happens first so it does a sum it does a total first before the division happens and we know that this is the correct value because we need to be dividing the three cents with the twelve dollars and ten cents so again just as a word of caution especially when you're working with ratios make sure you do the aggregation first before you do the division that's it that's your introduction to calculations hopefully you found it useful there will be additional videos and other types of calculations i'll see you next time

Channel: sqlbelle

Views: 22,307

Rating: 4.9675326 out of 5

Keywords: tableau calculated fields, tableau calculations, tableau tutorial, tableau calculation error, learning calculations, makedate tableau, tableau 2020, tableau for beginners, tableau calculations tutorial, tableau calculations survival guide, tableau date calculations, tableau basics for beginners, tableau tutorial for beginners, tableau training for beginners 2020, tableau desktop specialist certification, tableau tutorial for beginners step by step, tableau tutorial with dataset

Id: QimVQl5AoYM

Length: 23min 58sec (1438 seconds)

Published: Wed Sep 23 2020