We'll start by connecting to a variety of data sources. This will leave you with some great workbooks to practice on and develop on your own after this course. We'll switch focus to calculating user defined fields and use predefined Tableau functions along the way. We'll move on to customizing visualizations. And utilizing some cool features to distribute your work and export data. We'll also explore advanced features that will make your visualizations more user friendly..
Module 6 is all about calculating user defined fields. And we have 9 lessons in this module. We're going to start by using predefined functions. We've already used 2 when we created, uh, calculated fields in here. We use count d, which is distinct count, and we also use date diff, but you're going to use more in this module. Then we're going to move on to calculating percentages and showing totals..
We'll use more predefined functions when we apply the if then logic and apply logical functions. You're going to learn how to show the percentage of totals. discretizing data, manipulating text, and then aggregating data. So you've already seen this in action, and that's when you created a calculated field using a predefined function. The editor window will let you know if the syntax is incorrect for the function.
You're using, which is extremely helpful. I've also included on this slide a link to all of the functions that are available in alphabetical order in Tableau, and then another link for all the logical functions that are available in Tableau. So in Tableau, I'm back on the number of customers by state province map, and we're going to create another calculated field here. using the predefined max function. So we want to see the maximum amount of sales per state, province on our map. So we're going to go up to analysis menu and choose create calculated field..
This time we're going to scroll down until we see the max function and double click it. So and also on the right side it gives you the syntax for it. So you can give it either one expression or multiple expressions separated by a comma. It tells you what it will return and it always gives you an example. And that example is exactly what we're going to use. So in the editor window, it's already flashing inside the parentheses,.
We're going to start typing sales and we're going to get sales in there. Our calculation is valid. So we're going to go ahead and click. Okay. In order to see the max sales amount on our map, we're going to go to our field lists and under measure names, we're going to click and drag. Max sales, and we're going to drop it on the label in the marks card. So now you have both on the label, you have your count of customers.
And you have the maximum sales amount for that particular state. So let's take a look. Let's hover over North. Dakota. So they had two customers in North Dakota, and the maximum sales amount for that state is $705. And let's go ahead and save, and I'm gonna go back to the last sheet sales by region and bring up a new sheet..
On our new sheet we are gonna create a table and then we'll calculate percentages. of total sales in our table. So what I'm going to do is I'm going to select order date, the order date field, and I'm going to put that in columns and I'm going to leave it on year. And then I'm going to use state province under location, not the group that we created, but I'm going to do state province in rows. Then I'm going to drag the sales measure to the text mark on the marks card..
So we have our basic table structure here. Now let's, under location, let's drag state province to filters, or excuse me, we don't want state province in filters. Drag country region to the filter shelf and filter it just for the United States. So I put a check mark in front of U. S. and I clicked OK. So, we just don't have the Canadian provinces showing in here, in this table. Now, we're seeing the actual sales total for each year, for each state, and we want to see the percentage of the sales total, and we want it by each year..
Posts Related:
- Add & Update Excel Data to SharePoint List using Power Automate Excel Import using flow
- 20 Graphics Templates Bundle In One Free Download Sheri SK Photoshop Files Office
- Design A CSS Box Shadow Generator Using HTML, CSS & JS (Part 2) BlogSpot
So, to do that, we're going to go to the Analysis menu. - ID Card Make
You're going to hover over percentage of, and it gives you all of these choices. So since our years are in columns and we want it by year, we're going to choose column. So what's happening here, let's look at Alabama for instance, it's saying the Alabama sales total for 2019 is 1..27% of the total sales. for 2019. So that means that each column grand total would be 100%. And we can choose to show the grand totals here. And that's what we're going to do now. We're going to go back to the analysis menu, hover over totals, and choose show column grand totals. And now if you scroll all the way down at the end, of your table. At the bottom of your table, the last row is grand total and all of them equal 100 percent because we did the percentages by column..
Let's go ahead and add another field to this table. Grab the category field and drag it to rows to the right of state province. And since we are still doing the percentage by columns, So it's now showing, it's just broken up in categories now, right? So the categories are there. But at the bottom, you still have your 100% for each column's grand total. So you can add more detail, unless you change the way the percentage is being calculated. Like, we're not going to change it. It's still going to calculate to 100% by the column..
And we'll name this sheet, and I'm using the percent symbol, so percent sales by category and state. And I'm going to save. Now we're ready to move into applying the if then logic. And so we're going to be creating more calculated fields in this section using some predefined functions. And so we're going to bring up a new sheet for this. And on this sheet, let's go ahead and put order date in columns, and we're going to do profit to rows and sales to text, or label rather..
And using Show Me. We're going to convert this into a text table. So we get the profit and the sales by the year of order date. And I'm going to go ahead and collapse show me. Now we're ready to create our calculated field. So I'm doing it from analysis. And this one, we're going to name profit status. And so, with this one, we're gonna just type it in, and then I'll explain it to you, and we'll use our shortcuts so we aren't making too many typos..
So I'm gonna actually collapse this list of predefined functions on the right side, and we're gonna type the letter I, and we're gonna get the if function in there. We'll get it all in, and then I'll explain it. And then we want... The profit field, so I'm going to start typing profit and select that measure off the list. And then I'm going to type the greater than symbol and a zero. Start typing the word then and it is a predefined function,.
So go ahead and get it in. And now we need to put the next thing in single quotes. It's a text value that doesn't exist in our data set. So we're going to do a single quote and type the word profitable, and then close the single quote. Now you're going to start typing the word else, and you want to grab else if off the list. And we're going to use the profit field again. So I'm going to go ahead and grab that measure..
And as soon as I see it on the list, I stop typing. I've made so many typos. We're going to do an equal sign here, and a zero, and we're going to start typing the word then again, and grab it from the list. Now at this point I'm going to press enter. I don't want to scroll across to have to see the rest of the function, so I'm splitting it up. And because I pressed enter, it tells me the calculation contains errors, and that's because it's not complete at this point. So I'm on the next line, and in single quotes..
I'm going to type BREAK EVEN, and then I'm going to start typing ELSE, and I'm going to grab ELSE, not ELSE IF this time, just ELSE, and in single quotes, I'm going to type the word LOSS, and then even though it won't force me to scroll, I'm going to press ENTER again, and then start typing the word END, and we're going to grab END. I like my if and my end to be at the same margin level so I can make sure that I have it there, but it's not necessary to do that or even to break it.
Down by, you know, using enter at all. I just don't want to have to scroll across to see a full function.
So this says, if the profit value is greater than zero, then show the word profitable. Else if, meaning if it's not greater than zero, But it equals zero then show the word break even if neither of those is true So it's not greater than zero. It's not equal to zero. Then we want it to show the word loss Let's go ahead and click Ok,.And what we're gonna do is we're gonna drag our profit status field two rows in front of measure names So now you can see, based on the value of the profit, It's either break even, loss, or profitable, based on our if then else if then else structure that we used. And we're going to name this sheet profit status by year,.
And save. Let's make a copy of the profit status sheet, so again, I'm using my control key. Clicking and holding and just dragging it slightly to the right. And I'm on my profit status by year two sheet now. So we're going to create another calculated field here. Let's go back up to analysis and grab it. And this one, We're going to name simple profit status. Now I'm going to show you something. If you expand, so you can see the right arrow and you see all of the.
Fields that are available, go ahead and scroll down until you see the if. function and select it. And if you look on the right, you can see that that tests a series of expressions returning the then value for the first true expression. So we did if and then an expression, then do this. If that expression is true, then this. If it's not true, go to the next one, which is else if..
Evaluate that expression. If that's true, then do what And you're not limited to how many else ifs I believe. I usually limit myself to, some other languages I use, seven. But you can have a huge long one, if necessary. Now I want you to click on the one that says IIIF. That stands for immediate if. And this is the one we're going to use for this simple profit status. It checks whether a condition is met and it will return one value if true..
Another value if false. You can have an optional third value or null if unknown. So it only allows you to have three different things in this one. And so it is limited. And so I'm going to go ahead and double click immediate if. We'll build it and then I'll explain it. I think this one is a lot simpler than the one we did previously. So we're going to use the profit field here and then we're going to do a greater than symbol and we're going to do zero..
Comma, and now we're going to do in single quotes, oops, the word profit, close the quotes, another comma, and in single quotes, the word loss. So we're telling it, if the profit is greater than zero, display the word profit. If it's not, display the word loss. And we're going to go ahead and click okay. And now what we want to do is we want to swap out our profit status calculated.
Field for the simple profit status one. So I'm going to go ahead and delete the profit status pill that's in the rows. And I'm going to drag simple profit status up to rows before measure names. So now we're either getting profit or loss. No breakeven for this one. And we're going to rename this sheet to just profit or loss. And as usual, save. We're going to use another logical function here..
We're going to use the logical function of and. And we're going to nest it within the if then construct. So let's bring up a new sheet for this. And on this sheet, let's drag sales to columns and quantity to columns. And the first thing we're going to do is we want to make both of these dimensions. So right click on sum of sales in columns. And instead of making it a measure, we want it to be a dimension. And do the same thing with your sum of quantity..
And so now what we want to do is grab our market segment field and drag it to rows. So we're seeing sales and quantity. for market segments. Now we want to make this a dual axis. So we're going to move quantity from columns. I'm going to just click and drag it onto the sales portion. Of the graph and when I see the dashed line, I'm going to drop it..
So now I have a dual axes chart and what we don't need is the measure names card over on the right. So I'm going to do it's drop down and hide that card. All right. So now we're ready to create our calculated field and it's based on the market segment field. So just another way, so far we've been using the analysis menu for this. But what we're going to do this time in our fields list, right click on market segment and choose create and then calculate it field..
So when you do it that way, it includes the field already in the editor. We're going to name it consumer segment and we're going to click before the market segment and it's open bracket in the editor window. And we're going to start typing if. and grab it from the list. So you're going to press your end key to get to the outside of the.
Outer bracket on market segment. And we're going to type an equal sign. And in single quotes, we're going to type consumer because it's a member of the market segment. So we need to put it in single quotes. And now we're going to use The logical function and so I'm going to start typing the word and get it in there and then we're going to go for the sales.
Field we want greater than equal 1, 000 and I'm going to use the logical function and again and And now this time I'm going to press enter so I don't have to scroll across We're going to grab the quantity field And we're going to choose greater than equal five. Then we're going to use the then function. In single quotes type the word true. And then we're going to use the else function. And in single quotes type the word false..
And so I'm getting a my calculation contains errors. Let's see. And that's because I need that end. function. So I'm going to do enter and then start typing it, grab it. And now our calculation is valid. So we gave it multiple things to test, right? The market segment has to equal consumer and the sales has to be greater than.
Or equal to a thousand and the quantity has to be greater than or equal to five. If all of those things are correct, it will give us the word true, or else it will give us the word false. So let's go ahead and click OK. So now we're going to drag our consumer segment. to rows in front of market segment. And so notice, first of all, since we said that it had to be consumer, right?.
The only true is showing consumer. And it's only showing those things that meet the criteria that the sales are greater than or equal to a thousand and the quantity is greater than or equal to five. Let's Let's grab quantity in our field list and drag it to tool tip on the marks card. So now in the true section, if you hover over any of the marks, you'll see that the sales are greater than a thousand and the quantity is greater than or equal to five..
And so the other thing we can do is let's right click on market segment. And show filter. So that's a way of getting a field in the filters shelf as well as showing the filter on the right at the same time. In the filter shelf, we're going to do the drop down arrow and edit filter. And let's just get rid of corporate and home office on this one, and apply, and click OK. So we're just seeing the true and falsest for consumer. If we hover over any of those marks on false, like the first.
One, the sales are not high enough, and nor is the quantity, right? So, they're still in the right segment, but they don't meet all of the criteria. And over on your filter card on the right, We can go ahead and click all again if you want to see all and we decide that we're going to hide the field label for rows. So I'm going to right click on either consumer segment or market segment and just hide field labels for rows..
And this sheet, we're going to name consumer sales. We'll put greater than equal 1000. Oops. And quantity, I'm using QTI, greater than and equal to five. And so the other thing I decided is let's go ahead and filter it by consumer again. So I'm going to go ahead and use the card on the right to just filter by consumer..
And then I'm going to right click on the filters in the filter shelf and uncheck show filter. So we leave it like that. So, let's go ahead and save. And our next lesson here is discretizing data, which just basically means changing a field from continuous to discrete. And so we're going to go to an existing sheet for this one. We want the days to ship by subcategory and category sheet..
So I'll point out that we have the week of the order date showing on the x axis at the bottom, and it's a continuous unbroken line. We're going to change that. And then we're going to do that is we're going to right click on week of order date in the column shelf. And down toward the bottom, we're going to select discrete instead of continuous. So the whole thing flips. Now you don't have an X axis and each week is acting as a at the top of the visualization..
Now, to me, this makes the visualization a lot more complicated to read. So we're going to right click on the week of order date in the column shelf again and change it back to continuous. Now, let's go to the previous sheet, the sales and quantity by quarter sheet, and we'll do a similar thing with this one. And we have a dual axis here. So let's see what happens if we change quantity to discrete. So I'm going to go up to the columns shelf and right click on sum of.
Quantity and change that to discrete. And now you're seeing the quantities. Kind of like all grouped together and we're going to change that back to continuous as well. And then we have to make it a dual access again. So I'm going to grab the sum of quantity off of the column shelf, drag it over to sales portion of the visualization. And when I see the dashed line, drop it, or we could have done control Z to switch.
It back as Go ahead and save your file. And so, let's go back to our very last sheet in this workbook, and that would be our consumer sales graded and equal to a thousand sheet, and then create a new sheet. And on this sheet, we're going to use a string function to manipulate text. So, let's go ahead and grab product name and put that in rows..
And we'll put the sales field in the text box on the mocks card. So we have a list of all the product names and their sales values in a table format. So now we're going to right click on the product name field in the field list. We're going to hover over create. And we're going to create a calculated field again. And we're going to name this one,.
StartsWith3M. So product name is already there, because of the way we came in here. And we're going to click in front of it, making sure you're outside of the opening bracket. And we're going to start typing the word start. So you'll notice the function starts with. That is a string function, or a text function. Now the thing is, when I selected it, right, it didn't include product name in the parentheses. So I'm going to grab product name, and do, Control X, click inside the parentheses,.
And control V. So now it's in the right place. After product name still inside the parentheses, but after it's closing bracket, we're going to do a comma, and then in a single quote, we're going to type 3M. So we want this one to show product names that begin with 3M. We want to focus on 3M. And we're going to choose Apply, and click OK. So let's drag our Start With 3M to the rows shelf in front of product name..
And so, You'll see it gives us either a true or a false. If I go ahead and sort the starts with, it brings the true up top and you'll see all of the product names that begin with 3M. And then we have all of the product names that do not begin with 3M listed next to false. So starts with is, a string function in Tableau..
