Zen Master: 3...2...1...GO! Two-Minute Tableau Tips and Tricks

hello everyone.welcome I would like to know how many of you learned anything this week excellent excellent how many of you met cool people doing astounding things with data some okay how many of you met cool people doing business needs with data yes excellent great glad to see it's all represented well we are gonna continue that trend right now and we are gonna learn how to do more cool things with data but we're gonna do it very rapidly so what is better than ones n2 right we're gonna end your conference with a bang and I am gonna turn it over to Jeff and Luke and they are gonna blow your mind two minutes at a time everybody ready yeah all right let's give it up for two minute tableau thank you everyone you may have seen 50 tips in 50 minutes the next year I think we did 86 tips and 50 minutes that's with my co partner over there Andy creel and then we topped it last year with 50 charts in 50 minutes and I'm Lorna and Ann Jackson took over the the speed tips so Luke and I got together started thinking well what if we didn't do speed tips what if we slowed it down a little bit and develop things over a two minute period of time and so that's what we're gonna do for you today my name is Jeff Schafer I'm the chief operating officer and vice president of IT and analytics at a company in Cincinnati Ohio and also adjunct professor at the University of Cincinnati I'm a four-time tableaux Zen master and author co-author of the big book of dashboards yeah it's gonna be tough to top him he's done everything I'm Luke stanky I am a founder and partner of a analytics consulting company you know when we do this at a high level we just go off on our own and start a company and do this I'm a one-time Zen man sir and and protege to Jeff I guess today I'm gonna just try to keep up as we go and thanks for the applause earlier it definitely is the it's above what I expected for the very last session of conference so first of all thanks for coming out for the last session we've made it we're almost there one hour to go all right so what to expect today we're gonna do lots of different charts we're gonna try to bake in tips and tricks along the way if you're new to tableau we'll try to have some beginner tips if you're more advanced in tableau we'll try to cover some more advanced topics along the way we'll point out that some of the things you see us do fast or really just a result of doing it all the time so we would encourage you to practice as you build makeover Monday workout Wednesday and and all the other community projects and then if if time permits and technology works we may we may phone a friend here so all right so off to start do you understand tableau question is do you remember this movie I don't know is the matrix you know had a choice between two pills I think it was blue and red in tableau we have blue and green we're gonna talk about blue and green especially as we dive into some of the things that we're gonna do maybe do a little date identification and we'll talk about the blue and and green pills and how those affect things you're also not going to see Luke where I use show me I don't actually I disabled my functionality on this so it just doesn't work it okay so he might you show me I don't know we don't use show me because we we just don't find show me all that useful I refer to show me as the pill pusher it just moves pills around on the canvas right blue pills and green pills kind of moves pills we're gonna move those pills manually in some cases double-clicking to speed things up last but not least we'll show you a little bit how that happens this workbook will be on my tableau public page hopefully by the end of today so you'll have all these slides you don't have to take pictures or write any of this down so neither of us from making edits right up here well everybody was walking in we may or may not have added slides in the last few minutes all right so to get started I'm gonna just start with some basic things I've pre formatted my work look for some things and I've left my workbook unformatted for others just to kind of show you some tips or tricks along the way so the first thing I'm going to do is work up a couple charts all pretty quick I think and then we're gonna put them on a dashboard and do some some dashboard stuff so real quick I need a couple charts this dataset that I'm gonna use for this one is just web analytics data happens to be website analytics from data plus science com the data set doesn't really matter it could be any data set but it's web analytics data so I'm going to double-click pageviews that's going to put that on my rows and I want to see those pageviews by some sort of date so I'm going to double click my date and that's gonna go to my columns now I want to see this by day so I'm gonna go down and just change this to a day I'm gonna give it a continuous day just so we have all of the web visits by day so here's the first tip that I want to show you this one I use all the time it's a very basic super easy tip to use but we want to format this y-axis and so I'm gonna right click on that to format to bring up our formatting menu and in the number formatting on our axis scale the tip or trick is that you want to use the custom formatting first you're gonna go to custom formatting and format the number how you would like it I don't want any decimal places for example and I want to put that in units of a thousand once I formatted this then I'm going to click on custom because it's going to populate this number formatting for me it's gonna put in the number sign in the zero and all of that but you're going to notice way down in the bottom of my chart down here you're gonna see that there is a zero K on my access line and in often times I just want to get rid of that so first simple thing I'm gonna do is put a semicolon and a space and that's gonna disappear so real quick tip or trick there I'm also to clean up I'm just gonna double click this and take the word pageviews out and just get rid of it so I have a super clean chart I'm gonna repeat that on in a second chart here and I'm gonna do the exact same thing but this time I want to see things by day of week so I'm gonna double click my page views again I'm gonna double click my date you'll see I'm building charts pretty fast here this time when I select my date I'm going to go to my date part here and choose the the weekday I want the day of week and so there's my day of week and I kind of pre-formatted some of this stuff on here but the tip or trick on this is to get rid of some ink on your bar charts sometimes we have these large bar charts that fill up our dashboard we can reduce that ink which will you know the eye draws to that and so the tip here is I'm gonna put a dark border on the chart and I'm gonna change the color of it to the background and that will give me a real clean bar chart that I can add on there I think down here I pre formatted something so I'm gonna have to let me just fix my access down here there should be dark text and not blue text there we go and in this case I'm just gonna get rid of my header that's chart number two number three I'm building four charts for a dashboard here maybe I want to see what blog posts so I've pre grouped some of my blog posts into a topic in this case what I'm gonna do is I'm gonna bring that group that I did over to the rows just to see the group of blog posts that I have I'm gonna bring my pageviews up I'm gonna sort it real quick and I'm gonna add the label of pageviews so there's another one real quick in this case I'm gonna get rid of my access again there's my third chart for my dashboard not much tips for that but I needed a third chart fourth chart we're gonna do some mapping tips and tricks so we're gonna map some data now the first thing that you may notice is over here I have city and country they're not together they were but I took them apart and I want to create a hierarchy so I'm gonna double click or select multi select both of those pills I'm gonna right click on that and I'm gonna create a hierarchy just go down create a hierarchy I'll call that location and I have a hierarchy that quick unfortunately City alphabetically is before a country so I need to flip them so I'm just going to drag country up and it'll go on top now they live together when I double-click City longitude will go to my columns latitude will go to my rows city is gonna go to my details and I get a map now this map is not really useful yet because I don't have any data on it so I'm gonna put page views on size just to see what cities are getting the page views across the world so the tips this I love this tip for mapping it'll give you so many opportunities to make custom maps without going into map box so what do we do we go up to map we go to our map layers we are going to uncheck the base layer and we're I don't need land cover either so I'm gonna take that out and I'm gonna add coastlines now you say well that's kinda useful but what what did you do differently here well by taking the base layer off what I can then do is right-click on that and I can format the map and I go to shading and I want to change the pane and when I change the pane color of a map without a base layer I can get whatever color of a map I want and so that gives me a nice map now you'll see I also have white lines in there and light light lines that's another interesting tip that you can do you can go back to your layers and it's kind of counterintuitive but if you change your layer here to dark it'll change those lines to dark and so now I have a dark line with any color I want on my chart the last step is of course build a dashboard we've all watched as I double clicked pills here to create a chart but how many of you double-click to create a dashboard I mean can we do that so I'm just gonna find here I call it one web to web three web and four right so double click one two three four I have a four-quadrant dashboard that quick in a tiled format just with double clicking one two three four right and then maybe I want a text box at the top I'll just drop a text box on the top of it I'm going to actually I should copy my text here so I don't have to type it so I'll just copy this and then I'll drop a text box up here at the top I'll make it really big because we like big titles and I cannot lie and about four people got that joke that's great doesn't get much better than this folks so you know those are the jokes today we may have you know better tableau tips but there we go we have a finished dashboard with a title I'd probably add a subtitle and some other things with that I'm gonna turn it over to Luke all right it's Friday I'm just gonna lighten things up with some memes every time I transitioned back in from Jeff but other than that things are gonna get real serious really quickly and we're gonna build some really serious business visualizations just as a heads up to the data set hopefully everybody's familiar with it if you're not we're gonna use the superstore data for all these visualizations it's basically digital sales for retailer and the sales come in daily from in this case I'm using January 1st 2015 all the way through the end of 2018 and just as a reminder it has data for 48 states in Washington DC and several lines of business which they call category and subcategory so that's just gonna set up the context of building these visualizations that said we're gonna talk about how to build this dashboard well actually take this dashboard and completely break it apart and do it completely different because no one would ever make a dashboard that looks exactly like this not ever in their workplace and where we're gonna start specifically is with when we zoom in and take a look at this chart how many people and be honest with me have had to make a chart where they show the top and bottom performing parts of a business that of course it happens all the time the one issue that happens when we build this is we create two visualizations where the bars look like they're the same length however this axis goes to 450,000 and this one goes to 5,000 so I'd have to like do some like funky adjusting of this chart and it doesn't look very nice instead what might be useful is if we brought all of those together onto a single visualization where we could see both the top and bottom without having to do a ton of formatting so that's what we're gonna start we're gonna build this chart and it if it looks intimidating it's not really what we're gonna do is we're going to build two sets and three calculations and two of them are gonna be fairly straightforward with that we're gonna start by building a four asset right away and we're gonna do is I'm just gonna search for state in my dado right click and then go to create and then set and in fact I'm not gonna spend too much time building these but I'm just gonna say hey this is where you build a set hopefully everybody's learn this but we're gonna build our a top set and a bottom set when I'm on general make sure that you are using all and then going to top by field building that out just to show you what those two will eventually look like is we will have I lost one visualization along the way but will have two sets that we're going to create one for the top and one for the bottom once we have those then we're going to create two calculations and now my calculations are missing so I'm going to fix that in a minute but what we have here is a calculation that says if the states are in the top 10 then say top if it's in the bottom then showed in the bottom 10 else we're going to do these two little quotes and for second calculation since it's not showing up I'm gonna go and actually build that calculation for us right now I'm going to zoom in ctrl shift and zoom and I'm just gonna say if it's in the top or state is in the bottom then let's return the state name else we're gonna say other states and we'll say end so that's pretty simple too simple calculations that we've made and two sets after we've built those two calculations which I'm just going to cancel because I already have that saved we can actually build that exact visualization by the way I just want to point out that this calculation what it looks like is that's also broken which is no surprise okay so we end up now with is you'll see we have Alabama which is neither in the top or the bottom so it's another state and it's blank if we look at California it is at the top and it is showing the state of California we're gonna build this visualization by taking my first calculation here and bringing this out on to my visualization and you'll see this all looks the exact same but as I pull this out and then whole state away will now have a list of top autumn and all the others of course we can always do manual sort we're just going to click-and-drag top to the top and bottoms in the bottom and then I have all my other states unfortunately all my other states are not sorted so I can just click on state here and then click the sort button and it will sort that dimension for me what you'll also see is I have this bar that's really long it takes all the other states and it sums up sales for all the other states so how can we get all other states to show up sort of in the middle of all those others we can use the include function the include function will include state in it and we're going to do an inline calculation what I'm going to do is double click on sum of sales here on the columns shelf and it's going to open up the ability create a calculation inline I'm just going to use the curly brace the function include state and sum so what this is going to do is it's going to return the sum of states by state and the reason I'm not using this calculation is because I don't have the state dimension any longer on my view I'm gonna hit OK and you'll see that nothing is going to change well I've already done the sum of states inside that level detail calculation if I do the sum of a sum I'm just back to the same number but if I change this from some to average then I have the bars that I'm looking for finally we could just add a little bit formatting I'm going to sort of jump in cheat a little bit and say oh we had some formatting add some color formatting and we'd end up with that exact same visualization along the way so that's our first chart that we've updated we've taken two charts and actually made a single chart from it the next chart I bet no one's also made this chart ever police measure names and measure values on color and created this chart but you know what the issue is is we've got three different dimensions here one is showing sum of sales adding up to a very high number profit is both positive and negative and then profit ratio is as a percentage and it's not a very large percentage so we end up with just this gray box on color ratio so what can we do to actually make this useful or I'll make the legends useful while we can right click on measure values on color we can use separate legends this way we have three separate legends once you have three separate legends perfect now you can discern the values themselves except for one thing when you use legends on the visualization you probably shouldn't have three separate legends you should probably just have one number that you're actually highlighting in this case I want to highlight profit ratio and I don't really want to put color on any of the other values there's one issue though when I'm using measure names and measure values on color I can't go anywhere on my visualization to remove this legend I have to do something else so instead what I'm gonna do is I'm gonna go right into the color I'm going to change my palette to custom diverging and I'm going to set these colors to white so when I set the value to white I set this other one to white I bet you would never guess what's going to happen especially when I use stepped color of two but it makes it into an all-white view so it looks like there's no color formatting on it I've courted do the same thing with the next one but I'm gonna save ourselves some time and then of course I wanted to highlight profit ratio so here I've just taken the time to take the next step and format the next two dimensions my issue though here is when I'm building my tables is I don't like all this extra color I only want to show just like a little bit of red not like overdo it I want to make it a little more subtle so what I like to do is I like to use a function it's a really simple function and I call it zero what is in zero well if I edit it and I zoom in if you can't see it's just 0 dot 0 and 0 dot 0 is gonna be a placeholder and you can imagine right by adding 0 dot 0 it's just a column now in my data that holds 0 I'm gonna click and drag that out of my view and I'm going to change this to a dimension and once I have it as a dimension you'll notice I now have a mark right in the middle it is it's a little rectangle or a square and I want to just make that a circle but it's in the middle and I don't like the fact that my values are kind of second second fiddle to the actual mark itself so what I'll do is I'm going to go down on my axis and I'm going to edit the axis I'm going to set this value to through a lot of practice by the way set it to negative one and point two that's gonna get my values shifted off to the right and then if I just move my labels over as well I will have what looks like kpi's with a little bit of color off to the right of it finally we can add a little bit of formatting to this to make it look nice we all know this trick we can just right-click and copy the formatting and then we can bring that over and paste formatting and of course oops once we bring over that formatting I'm going to paste it and then I have my fully creative visualization because I had the formatting already on another view and that is where I'll leave you with the next bit all right so for the next section I'm gonna take us from simple map tricks and basic dashboard tricks to what's next data densification why not so we're gonna jump from the very simple to the very complex this this is a very complicated topic and I'm gonna try to keep this this simple for what I'm gonna at least show you today I would not be standing here talking about data densification if it weren't for some people that have come before me so the first person I want to talk about is one of the original tableau Zen masters he wrote blogs and things on the forums and talked about webinars and things there's a 2014 video that's 5 years old where he is talking about some of the things I'm still talking about today so that would be Jo Mako and yeah big round of applause for Joe and the second person that I have to mention if you missed his session at the tableau conference he's also equally talented has written more about data densification than anybody on the planet his blog posts are still valuable today even things that he wrote as early as 2012 and 2014 if you missed his session at conference this would be the session to to check out on a recorded session and that would be Jonathan drummy so thank you Jonathan there's lots of other folks that have taught on this Jonathan has a slide of about 40 or 50 people in his presentation that have all done these these topics so shout out to all of those too many to name up here so a couple concepts I'm going to try I'm really using Joe's terms for most of these things they talk about sparse data what is sparse data sparse data is where it's not completely filled in you'll notice on the left-hand side I have a number and it's sort of missing on the right hand side I have dates but they're not complete dates I go from November 3rd to November 14th to November 27th and so that's one concept that really you need or often need data densification to to solve one of the methods for data densification is domain completion that's where you'll see on the left hand side i'm just using subcategory and category and there's a whole bunch of missing ABCs there's just they're not there and so how do we get those there and domain completion happens when there's two blue pills opposing each other you'll see on the rows and columns they're there against each other and I use one of the most magical functions in tableau which is the index function we use that a lot and that will turn on data identification in this case it'll complete the domain and you'll get the ABCs the second concept that you're going to see is domain padding domain padding is different in the sense that you have what Joe refers to as a range aware pill a pill that has some sort of range and he doesn't say date because it also works with pins so a bin or a date William you could start at 1 and go to 100 in a bin and you can densify inside of that or you could have November 3rd and November 14th and you can densify in that now you'll see at the top I did just weekdays by category you'll see the ABCs just 1 2 3 and then there's no fourth of the month or fifth of the month that's missing and then down at the bottom you'll see that there's a couple different techniques show missing values and all of a sudden I have these values that I didn't have before so those are the concepts that we're gonna talk about here Kevin Flair Lidge has a great post that he did a Kent Flair Lodge excuse me boy Kevin works for me and I'm still mixing them up Ken Claridge has a great post on data scaffolding and he solves this particular problem that was asked on the forums this this problem right here where somebody simply wants to add 0% in all of those in that table and all those values where it doesn't exist and he talks about how to do it with tableau by bringing in dates for every day of the year and he does that with tableau a.join and he does that through prep so great blog post definitely check it out however I'm gonna do that same thing and we're gonna do it without data scaffolding so let's figure out a couple other ways that we can do this and there's there's many different ways to do this and scaffolding is certainly one way but let's do data densification so a missing values here you'll see that I have two blue pills that that are opposing up their customer name against a month so there's a couple different things I could do with this let me do a I guess the more complicated way maybe a couple different steps here I'm gonna do an inline formula on my marks card like Luke did double click on there I'm just going to type index open paren and close paren so just index I'm just typing it right there on my mark shelf and that will give me an index I'm gonna click the little icon on it and when I click the icon I'm gonna put that to label so that you'll see the index now you'll see the index turned on data densification when I put it on label it showed it what is index - it just counts from one to twelve across so I'm kind of I have all the points I need I just need to make all those numbers zero except for when there's a value right so I'm gonna create a calculation just a quick calculation that does what and so here's my calculation I'm gonna say when the profit no all right if that's false then I want to use profit ratio so if profit ratio exists I want to use it otherwise I'm gonna use that index one two three four five and what am I gonna do I'm gonna subtract index from index 5 minus 5 is 0 4 minus 4 is 0 3 minus 3 is 0 right so I get 0 and that calculation right there I can put that on the label instead of index and what do I get I get a bunch of zeros and I get the value when the value is there so you'll see the 13% that I'm trying to get is 12.5 the rest of them are 0 so in that case now I can take the profit ratio off I can format this pill to a simple percent again these tips are pretty easy the concepts little more difficult right so we'll just do 0 as a percentage with zero decimal places and now I have all the zeros that I want in my table without scaffolding any data to this without doing a union I just turned on data densification okay let's do it another way maybe I can do it a different way without even doing that index calculation and and doing an index minus an index well another way to turn on densification that's even easier is we can go up to the pill I'm sorry over here on profit ratio I'm sorry I'm on the wrong pill month Thank You month so I'll go up to month up here and I'm gonna click on show missing values that's that that's that highlight that I pulled out in that slide for you and I'm just gonna turn on that value of show missing values so we're gonna click that and you'll go oh wait a minute Jeff nothing nothing happened there's there's no missing values there well I didn't tell tableau what to put in that field so what you do is you go to the profit pill over here go to format and right down at the bottom you'll see where it says special values and that is where I'm gonna put my missing value it's blank right now and I'm simply gonna put in here zero percent now when I do that you'll say well wait a minute Jeff that didn't work either like nothing's coming up in here like nothing's showing up well let's use that magic function I told you about let's go up here and add that index function so that it turns on data identification remember the index function is going to turn on that data identification I don't have to do index minus index whatever's in that value that textbox is going to show up like TC 19 right so there's how we can do that without doing on index minus index all right so let's bring this into a practical application let's build a calendar and see if we can apply these concepts to to a calendar so we're gonna we're gonna use superstore data here I'm just gonna widen this up make sure I'm using superstore 20 and 19 I'm gonna put order date on our columns I'm sure most of you know this with your ctrl key or your command key you can duplicate pills next to each other for fast so I'm gonna do here and inside of year I'm gonna do week day right I'm gonna duplicate it again down here and I need a month I'm gonna go down and build month so there's our month so I'm gonna duplicate the pill one more time because we need weeks inside of month so I'm gonna go to week number and what do you see you see a bunch of ABCs just like we had in our table example from from Ken's blog post so what can we do here well I guess just to get rid of ABCs let's just let's just put a day in there so I'm gonna move order date over to text you'll see that it has year in here now before I change this today because I'm gonna I want the first of the month in the second of the month and the third of the month I'm gonna go up here and just pick one of these date pills and show the missing values like I did before and you might say hey that right there solved our problem we have values everywhere and it's true there is a 2000 II where and if you take that down to a month there is a month everywhere and if you take it down to a week there is probably a week everywhere the problem is is if you go down today there isn't a day everywhere and so we're back to the same problem that I'm now missing January 17th I'm missing January 24th I could do the same thing I did before I could apply the special index function right that magic function so let's try that I'll go in here index will type that you'll say well wait a minute nothing happened well that's because in this case I have to tell the index how to count I have to tell it what order to count in so I'm gonna go in here and set it to compute using the order date and you'll see it filled in so now I have all of those dates so this is great but now I may want to take it a step further here I may want to build a calendar table out so I went ahead and and just kind of did that for you just to save some time but this is really to kind of show you what it's doing it's it's those missing values in there throughout the the period that it's that it's counting right it's filling in for us with that I'm gonna turn it back over to Luke well he's getting set up here let me switch the box here Oh Jeff those table calculations are a little bit magical aren't they they lots of practice light a hand and they just do the trick so what we're gonna do is we're going to do a little bit more with table calculations to see if we can really make them work what we're gonna do is we're gonna build this visualization you see up here it just has three table calculations and what you'll notice about it that I want to point out is that we have month-over-month calculation we have a year-over-year calculation and then we have that for all of 2018 should seem fairly straightforward this is like it should be a pretty simple table to build however I want to show you why it's not as easy as it looks but doesn't take much to fix so we're just gonna start with all the years of data in that same data set we've got category on rows and we've got by month and year on columns and if I were to do a table calculation a simple month-over-month table calculation I can just click the drop down go down to a quick table calculation and choose percent difference when I choose percent difference sure enough I get a month over month difference and the reason I know there's a month over month difference is if you look at January 2015 there is no data for that month this is not a surprise we've all done this calculation before and in fact when we've done this calculation before and we filter to the year that we're looking for 2018 what happens mmm no data in January for 2018 so I now don't have any data to actually compare that January 2018 so how do I fill that in with information well it requires a little bit of work but we're actually going to build out first before that I don't want to I just would setup the problem a little bit what we want to do first is build our year over year calculation and to build that year over year calculation it's not too difficult we just need to manipulate our existing table calculation and there's a couple of things that we can do with our table calculation as is we can we definitely want to save this out so I could just click and drag this over to measures and release and I could save that calculation out I'm not going to do it that way because that's just not the way that I personally like to do it I like to create a new calculation click calculated field and then click and drag that value into the box and it will bring in the whole table calculation I'm just going to name this sales your month-over-month we'll remember that for later I'm just gonna hit apply and I don't have that calculation so that's perfect and then I'm gonna build another calculation straight off of this I'm just gonna right click duplicate and I'm gonna build my year-over-year from this as well just right click Edit and you'll see my calculation you'll notice the minus one that's saying look up go back one value it's a nice function that tableau has provided the lookup function I'm just gonna change these values from negative 1 representing go back one month to negative 12 and when I do this I now have my instead of month-over-month value my year over here I'm just gonna hit OK there as well so I've got my table calculations but I still have not solved my problems so we're gonna build out this visualization let's start by well how I personally do this is I'm just going to take sales and I'm gonna search for it I'm gonna I could bring out each value one at a time but the easiest way to do that I want to do this is with measure names and metric values I'm just gonna take measure names drag it out on rows and it shows up on my visualization as no measure showing I'm also just gonna take measure names and add this to filters and this works really well by the way when you've got a long long list of measures and you're like I don't want to have all my measures show up in my drop-down I only want to select what I want right away so I'm just gonna unselect this and I'm gonna say let's show sales sales month over month and year over year and hit OK nothing changed nothing showed up because it won't adjust on my visualization until I click and drag measure values on to text so there it is I've now brought those in without getting my really really long I'm going to do another thing that I like to do right away you'll notice my percentages they're whole numbers and I really want those to be percentages well we can format those from the get-go right away and the way we can format those is to multi select so I'm just gonna hold down control multi select choose the drop-down go to default properties number format percentage zero decimals and then tableau will override my visualization and bring that formatting in there's one other thing that I really like to do is you'll notice that on my my visualization here I want to skip ahead to this one you'll notice that I have month over month and year over year show and it's the same labels well I want to edit the alias of this and what I want to do is I like to bring in some little characters some additional characters to help with the formatting so what I'm gonna do hit OK bring that in right click edit alias paste that in and say month over month and hit OK and I've edited that alias I already have one that exists like that so if this ever happens which I'm sure you know just having a nice little space after it and we won't have any issues hopefully that's the best tip you walk away with oh I just add this space and I mean I could do the same for the other one I'm not gonna spend time doing it I'm just gonna add that formatting in for color right away too and now you'll see that I have like a little alright here's my sails and then here's the context a month over month and here's the context of year over year finally we've gotten to the point where we need to figure out how to do this how do we do this how do we get rid of this blank value I'm gonna show you a little trick on how to do this but I'm gonna set it up first by using an inline calculation of the last function and the last function does the same thing that Jeff showed with index except it counts from the number of spots from the last dimension yes from the previous last dimension so we have 0 on December and we know that the end of the year is always going to be 11 so I could just create simple calculation that says if it's in the last if it's less than 12 show it otherwise let's hide it oh this seems like it's gonna be pretty crazy it's just that really simple calculation I'm just gonna search for it show/hide click and drag that value out on my view and you'll notice that now that value is there it says show and of course if I go back far enough it shows well all you need to do to keep that calculation is to right-click on hide I'm kind of giving the secret away is then hide that value it's still on my view but it's now hidden and you'll see that January has those values I'm gonna get rid of last here but all I now need to do is right click and click uncheck show header and that we have those values brought in and take a look January now has that blank value brought in so I have a full table of data where I don't have to sort of cheat and never see that one month sure since I got an actually sometimes I get an extra moment I'm going to show you this calculation it doesn't really mean anything because we're going to talk about it there's a preview for my next section before Jeff comes up here this calculation returns complete months so maybe I want to just clean it up a little bit more and I would say all right let's bring and drag and drop that on my view and if I were to say be in you know on the 15th except this is 2018 in my data on the 15th I don't want to see you know November because it's not a complete month and I don't like to report until I have complete months well now I have this dynamic calculation no matter what what I bring in here and if I would change my date it would show the most recent 12 months of data so it's a rolling 12 months again I'm gonna show more about that calculation in a moment hello really Thank You Luke I don't know if you heard the story but but Luke was actually kicked out of the casino at the roulette table last night because he got in a heated discussion with the roulette table about what he considers to be an odd number so I don't know if he'll if he'll be back there today so Luke was doing a filter on the measure names there let me let me kick this over I had another thought and we were we were doing this in our in our rehearsal I do that one in a in a different way so I'm gonna just use the superstore data really really quickly here and just go up undo all of these so that we have what I need let's see let's do category subcategory we've all done that before and we'll pick a couple measures I'll just take sales well what if we want to create multiple columns doesn't matter what's in there but you know let's just say I want to add quantity or rank I could do what what Luke was doing with the page filters but another way I could also do it is just take quantity drag it right on top of the table and that will now be measure names automatically with my two pills instead of starting with all of them and working my way backwards so now I have the two in there and if I want to add profit ratio I can drag it in the table and kind of work my way up so just another shortcut I guess to do that so let's go back to our calendar and let's talk about this a little bit first there's no data in this we want no we're gonna put some data in it so maybe I'll just do a profit ratio let's let's put cut profit ratio on color that's gonna change the text and I'm gonna change my mark to square so that that's looking like a more useful calendar however even though I turned on data identification and I filled in all of the dates it's not missing any dates what you'll notice it because we're padding because we're using padding it only is gonna start at the first date in your data set and it's only going to end at the last date in your data set I can't pad before a date that doesn't exist so this data set starts at January 3rd in 2016 and then way over here in 2019 at the very end you'll see that it ends on December 30th so I'm missing the last calendar day and the first two calendar days so this is an interesting problem one way we could do this is go back to Ken's blog we could talk about data scaffolding we could join up all the real dates and we'd have a date for everything but let's see if we can do this in another way so what I'm gonna do is build a top level calendar that's what we just did so that's that's exactly what you just saw so that's a calendar that's gonna go on top with my data and then I'm gonna build a calendar underneath it that has all of the dates it's really interesting that I was working on this exactly the same time prep for a conference and then Ann Jackson did a great work out Wednesday a couple weeks ago and did the same technique so check out that work out Wednesday if you haven't seen it so let's do something similar here what is the trick to get these in here well the trick is is that you're going to offset it with this other calculation you're gonna basically create a different date in the data set so I called that date min max date and it's it's not really that magical it just simply says if the order date is the minimum date in the data then we're gonna make that date the first of the month right January 1st and if that date is the last date in the data then we're gonna make that month December 31st so January 3rd that first date I'm gonna move it to the first and December 30th I'm gonna move it one day to the 31st with that then lets me do and this is just a table showing that and I'll put them side-by-side is that the real order date is January 3rd the min/max date is January 1st and we're not gonna use that date for any data so you don't have to worry about what the actual profit ratio cuz that's that's really not the profit ratio for the first of the year but now we have the first of the date and we have the last date for the full calendar year in the data so now I can build out another calendar view that I'm just gonna call the base and my base is going to build out a calendar I'm not going to repeat the steps because it's the exact same steps I did before but instead of using order dates I'm gonna use the min max date I have the min max year the min max weekday and then when I turn on data densification what happens well I get everything filled in from the first to the second to the third and so on so now I have a full calendar from January 1st all the way down to December 31st and then what's the magic of that well now that you have a base and you have a top you can layer them in tableau put them on your dashboard make the top layer a transparent worksheet and you can have two on one right so now they're together there's no scaffolding required I didn't step out of this data set at all and just to kind of show you here I can I can actually check this here I'll just get rid of this one there's the first layer and then the second layer on top of it with the data so we can see that it's just really one on top of the next so that's finishing up the the calendar there with that I'm gonna pass it back to you Luke that's impressive I like that now we're gonna talk about simple KPIs and I'm gonna show this meme because it's definitely well when we create KPI is a great KPI often has a lot of backbone and structure that goes into it with additional calculations and Jeff and I had a long debate whether I should share this because you know KPIs they're so simple but they do take a lot of work and we all use them every day in fact when we make them we often make them you know the the old-fashioned way once again using the old measure names measure values but the thing with this set up when you use measure names and measure values you end up with not being able to control the space around your value of course you could measure values our measure names also on text but you still end up with the tooltip that is one value that you can't control for each of the KPIs so instead what I want to do is walk us through how to build this KPI that you see here and in fact what's nice about the KPI that we're gonna go about showing is that as I change the date in my data source and it updates it does do the dynamic shifting and rolling of the data to the most recent moment in the data set so what's nice about this KPI by the way the way that I have set this up is you there's also contextual data included in here if you're in the back it may be difficult to see you are able to see the year-over-year check or the month-over-month change to go with this visualization so you're getting more context within the KPI how do they look side by side well you can see one thing that's interesting is that as we add more space between these in our dashboard we again are not controlling the height on the top one but on our new KPI it's much more controlled and easier to understand and how do I do this while I go back to using my trick with zeros in this case I'm just going to create an inline zero and a couple of them by double clicking and typing min 0.0 and that just centers the values and it creates an additional marks card and for every marks card we have we can control the dimensions on that view or sorry the measures on that view but we're going to start by building out of all this in the KPI is using my favorite calculation I believe this is absolutely the greatest calculation of all time I'm not joking or being sarcastic when I say that that this calculation is the greatest because it always returns the max date in the data set and if you've mastered dates and you know how to do this you can figure out any date in your entire data set and build out any kpr the way that you want most recent month five months a year a year to date you name it you can use it with this calculation from there I always like to build this calculation I understand that this calculation looks complicated so let me break this down this calculation will always no matter what returned the moat the last date of the most recent month and the way we it's done is that we take again that max order date the moat the maximum value in our data set and we just add one so if you could imagine let's imagine two different scenarios that were playing out we've got two different dates we're working with we've got Jen some date in the middle of January and then we've got February 28th that we're working with non-leap year in our data set that middle section first what it's going to do is just going to add a date so max date plus one and then the date trunk function is going to bring it back to the start of the most recent month if we're at the plus one in March it's just going to stay on that particular date and then finally we're going to subtract one and no matter what it will always return the last day the last full month and guess what in reporting world this is exactly what we need to often return sort of skip all the stuff that we're in the middle of a month of and then ultimately using that function oh man Luke you've got another crazy calculation here what is exactly going on well we're gonna use that calculation to calculate the most recent month so you can see in the timeline we're taking reporting date which is the end of the month and then truncating it to the most recent month to get all that time period in between that's returning the current month we're then creating another calculation that returns the prior month and finally I'm gonna do another one that I always just include for that for the sake of it which is the prior year same time time period we use all these calculations to create our KPIs the first KPI that we're going to create is this top calculation which is the saying if it's in that reporting period in the most current period return the sales again I told you remember the the mean this is exactly where we're going to cut people's heads going like this which tells me there's lots of processing going on once we have that we have our current calculation we've got our prior calculation and of course from there we're done we have our main kpi's but no I also decided I wanted to show the difference so of course I had to create a difference calculation and then because I like two different colored arrows I create two different calculate calculations one with an arrow up when it's positive one arrow when it's down and all those calculations go in all eleven calculations go to creating one very simple looking KPI I know it's a lot of work to get there but it is completely automated when we do this so when we go to build our sales calculation we just type in sales and I'd bring in my current value I Delta my down and up arrow I add that to text and then I have all my values in there and then I just do a little bit of formatting by clicking on text three doubts and then formatting these values of course I'm gonna take my arrow values and put them all in the same row I'm going to take my delta value and put it behind it and then I will also just do some quick formatting of the up arrow to be whatever color I feel like and Jeff I'm gonna break some rules I'm choose blue and then put my down arrow and make that read once I have those done I'm gonna take my sales value because it is my KPI make it really nice and big on this line let's do like size 20 and then of course I like to label this I'm just gonna call it sales and change the color quick from there we have created our KPI for sales so that's all it takes I know it's a little bit crazy but that is exactly how I eat you KPIs every single day I do this every day thank you Luke we're gonna do a couple of fast ones here I guess it just to kind of finish up one I thought that I would put in here I'm gonna jump over whoops is this chart here so I think this popped up on Twitter this is a if you go to plural site plural sites a great site for video training technology training and so on I guess the disclaimer I should say as I said it's a great site but I own stock so don't buy it you know don't don't go to as plural site on my account but anyway this chart is what they show you when you take the exam I guess on their website you can take two tests and it shows you the proficiency and somebody asked on Twitter hey great chart can can we build that in tableau and so sure you know the left side is a stacked bar you know that that's easy I guess it's the main chart that kind of is makes you wonder why is that interesting well there's three marks there right there's an area chart at the bottom there's a line chart on top of it and then there's a shape and if anybody is familiar with tableau we can't get three marks on a single chart right we can do a dual access but there's no triple access any tableau people in here there's no triple access or any more or we'd like more of those to any tableau people yeah so we only have two to work with today so sometimes we have to trick tableau into getting things that we like so how do we get a area chart a line chart in a different color there and a shape on here so with this one I'll just kind of give you the answer the answer is it's really not a line chart so what did we do we faked a line by doing a stacked area and the top line is really just the top of the stacked area so all I did here was simply create a calculation to put on the top of it you'll see my cup my I've this dot position here that's where my dot is and then I have a bottom stack and I have a top stack so let's go look at that that skill I guess top stack that's the bottom stack here's the top stack so we'll just look and see not really a big fancy calculation is it right so if the person that's the shape is null then we're gonna add a 1 that's it right so where that shape is we're not going to add a line we're not gonna add a value of 1 but where that shape isn't we're gonna put a 1 so what that means is I'm just I'm basically putting something on top let's let's say else 10 and see what happens to our chart I'll just move this up to the top and you'll see hey if it's 10 I'm adding 10 on the top and if it's a 2 I'm adding a 2 on the top so all I simply did here is create a stacked area and then I use the measure names and measure values to trick the color I made the one bottom part of the area chart a darker color and the top line which is the stacked top a brighter color and so that's the tip or trick on that one for you sometimes you just have to get a little creative when you want to work with your marks you can use those measure names and measure values to do that quite often you can do lots of different tricks with that I'm gonna turn it back over to you so we're almost to the end but we have another tip you know how I said let's remember the month-over-month or the mom calculation it's my mom's birthday today so what we should do is call her in tableau you can do that if you didn't know in fact just quickly not only can you do that ignore my duck's desktop that's why my images weren't showing up earlier I didn't have all the images on there that I took for screenshots and whatnot for this you can send emails via tableau you just have to have a link I have this one dashboard I created and it's on tableau public and you'll see you can quickly fill in an email if you automate the fields for it or you can make a call and that's what I'm gonna do I'm gonna call my mom in tableau so I'm just gonna go by the way practical application right it's always important you've got a cloud like the salesperson wants to get a hold of someone so they want to see the number so then you'd like call straight from it and it's on your mobile so here is what I'm gonna do I'm gonna call my mom I've already loaded in my her phone number so no one gets her phone number and calls and says hi to her as well we'll do it all together once I'm just gonna go to worksheet I'm gonna add an action and I'm gonna say oh I already added it but that's alright I'll show you what we it looks like it's just te l and semicolon and then the calculated field of phone number so it's not simple by the way I just I'm gonna have it as a select action on a URL action I'm gonna hit OK and once I hit OK I tried this earlier the audio didn't go through so we're gonna call and then I'm gonna switch it over I'll show you so it's right here my wife's wishing me luck at my presentation right now and then I just can just hit call and then it's going to oh there we go is it going to call through it is there it is I was calling through I'm just gonna switch over speaker hey mom happy birthday so everybody else wishes you happy birthday as well I believe oh you just did my mom will talk to you later that said we want to wrap things up and we do have a little housekeeping of course you're finishing up this is it we were told after the session to go outside and take questions out there but it's the end so we can everybody we can just come up if you have questions or visit we have talked to Jeff Roy and of course don't forget to score us all fives because we're awesome just as awesome as everybody else everybody gets all fives today thank you right

Channel: Tableau Software

Views: 13,053

Rating: 4.9378238 out of 5

Keywords: Visual data, Visual analytics, Business analysis, Business analytics, Business analysis tool, Data analytics tool, Data Analytics, Analytics, Analytics platform, Cloud application, Business analytics platform, data analysis, data visualization, business dashboards, business intelligence, tableau, tableau software

Id: rnP7zP8J_7g

Length: 57min 53sec (3473 seconds)

Published: Mon Nov 18 2019