Tableau Tips Part 3 - TIL Zen Master Webinar Series

okay Thank You Dirk and hopefully you can hear me okay if you can't you can always leave or if there's a problem just leave a comment or a question and Dirk will be monitoring those two to respond to those throughout the webinar so this is the third in my tablet tips webinar series the first two were last week in the week before and those videos are both up on my blog at vizwiz comm and they're also on my youtube channel I did notice that somebody has already asked a question if the workbook will be shared yes it will it will be up on my blog and it also be on my tablet public profile and within the workbook itself you will also see the videos so I embed the YouTube video straight inside of the of the workbook so you can get it all in one place so all right so why don't we go ahead and get started I'm not sure if I have an hour's worth the tips this time Dirk so I'll just go is until I run out and then we'll just stop there so hopefully that's okay with everybody so what I'm going to show you here is how to basically how to create a mobile design so in this particular example you're looking at the best sales dashboard ever I particularly love the tree map and this pie with about a million slices so but we want to have a really nice mobile version for our sales team because they're always on the road so to create a mobile version you simply click on the device preview button on the upper left hand side of the screen when you do that tableau allows you to kind of scan through different device types and get some previews and things like that now for me I typically only ever use the default and I use phone so because if basically the reason I do that is because if something's bigger than the phone size I wanted to just use the default so I don't really worry about that but let's say that I want to create a phone version of this dashboard so I'm going to change my device type the phone I can pick the device type I'm going to just pick a generic phone and then I hit add phone layout okay great and tableau just gives you kind of this this border that shows you the size of the phone screen which is great the first thing I do is I choose this fit width option over here on the left hand side so under the size I choose fit width to make sure that whatever phone this is viewed on whether it's bigger or smaller it will adjust to the width of that phone so I click on that and then I usually just automatically change this height to match the height of my phone model so I'm going to change this to 667 okay and we have something like that and I can move some things around and you know I can I can reorder this but actually for my mobile version I have a series of sheets that I'd like to include so for example my mobile map I want to have much simpler right so I just want a simple map like this that just shows me the regions because I want to be able to just tap on a state and drill down into my sales for that state and the top five products so essentially I want a completely custom mobile view totally different than month and the experienced users will get on a device well if I scroll down through my list of options here and the layout you'll notice that none of those are on there none of those other other views that I built are available for me to use on the phone well that's because they have to be in the default version so I'm actually going to go ahead and kill my phone version there and I'm going to go back to my default here now in order to make these available I have to put them somewhere on the dashboard right so if I so what I like to do is I'm just going to go ahead and change my object type to be floating and I'm gonna and I notice I've got my mobile map here now I could just float this right here in the middle but then that shows up for my users right I don't really like that so I'm actually going to go ahead and get rid of my region labels as well so that's not quite what I was looking for well what I could do is I like to just kind of move it up here on the upper right and you notice if I move too far it turns into a red X and it disappears so I'm just gonna stick it there and then I'm going to go to my layout tab on the upper left so up here next to the dashboard tab you have a layout tab and you'll notice I can now specify the XY position so if I go back to my dashboard I can see my dashboard width is 900 well I can actually just change this to maybe like 950 in the exposition and that'll just shoot it off to the right-hand side and then I could maybe change the height to maybe make it really small like 10 by 10 so it's really hard for people to accidentally click on it now the reason I do that is because when I publish this to tableau server the users tableau will actually only render the this viewable portion of the screen you know the the portion that I have highlighted in blue right now that's all that's going to get rendered because this other object is off to the side so I just repeat that with the other objects that I need to float so I'm just going to do that again I'm gonna go here and make this 950 again and I'm just going to change my height to 10 and 10 and then I'll do it one more time with my top five products and again I'll change my layout and make it 9 50 and 10 and 10 I'd like you don't have to do the 10 and 10 but I like doing that just in case they appear for some strange reason the user will basically not notice it okay so I've got those three objects over here on the side somewhere so now if I go back to my dashboard tab and I click on device preview and I switch over to my phone version and now if I add my phone layout I'll go through the same steps again so I'll do fit with 667 okay great and I'm just gonna start removing some of these objects so I know I don't want my map I don't want my cumulative sales I can get rid of this profit ratio field and get rid of that I can get rid of that okay so now I'm left with just kind of a blank canvas almost looks like a blank canvas let me make this font a bit smaller and so it fits on one line okay there we go so now I have these objects over here on my on my custom layout here so I've got my mobile map okay so I can now drag that down put it over here I'm gonna make it I'm gonna actually change it to not be floating I'm gonna make it tiled and there we go so then I'm gonna go down to my my next one my mobile sales right and let's do the same thing there let's make this one tiles on check floating and make it tiled and then lastly go to my top five products and do that same thing again so now I have a customized dashboard for my mobile view okay so I'm going to make this Fitness higher view make this one entire view entire be great okay so now we're all set so now I can you know add my filtering as I want so I can go here I can say where is my use as filter okay so my use this filter is missing that's interesting okay but ideally what I'd want to be able to do now is to tap on a state and have it filtered down to the region so let me just go up to my dashboard I think I need to go back out of my preview let me go back here and I'm gonna actually add a dashboard action that's going to go I'm gonna filter so let's do you choose none of them I want to go from from my map and I want to update I'm gonna update my top five products and I want to update my sales okay and we'll do that on select and hit OK and now if I go back to my mobile version I can click on a non-central and I see my sales for North Dakota but notice my top five products are not showing well that's because I need to make I need to add this to context so let's go to that sheet and I need to put this filter on context so that basically forces the state filter to be applied first before the top five products are displayed so let me go back now to my mobile float and there we go so now we have a nice nice simple version here so the last thing I might do is kind of fix the height of these to make sure that they don't really change so again I can so now or I can even last so a few if I want to whatever I want to do and everything works nice and neat so that's a neat way to be able to have a completely different version from your default so again I just float everything off to the right-hand side of the screen all right next is what I want to do in this case is I'm looking at sales by day using superstore and what I want to be able to do is I want to be able to find the maximum date and use it to show the last end days okay great so let's go ahead and create a calculated field and I'm gonna just gonna call it by max date and for this I like to use a level of detail expression so I'm just going to mustachios and I'm just going to do max of order date and then put a closing mustachio essentially notice I didn't put fixed include or exclude on here when you don't specify any of those that implies fixed on everything so I think of it as like your grand total your ultimate grand total so this is looking at the entire data set and it's bringing back one value which is the overall max order date great okay so I've got my max order date now and what I want to do is I now want to create a calculation that shows the last end days based on that max date okay so I'm going to create a parameter and call this my last end days and I'm just gonna make an integer I'm gonna let my users type in whatever they want let's start with 14 you know okay and let's show that parameter control okay let me move the screen all the way but all right so we have the last end days and really what I want to do now is filter this view to the last n days okay so how can we do that well I'm gonna call it my date filter actually I'll be a little more specific I'll call my last n days date filter and what I want to do here is I want to use a date DIF fun and I want to say the difference at the day level between my start date which is my order date and my end date which is my max date and I want to say that has to be less than my parameter so I could just drag my parameter in and say I'm not saying less than or equal to because that would only return 13 days in this case because the most recent day is zero so really I want to do 0 to 13 and I hit OK there and then I can hit OK and now I have this true/false field so I can drag that to the filter shelf select true and now you'll notice down here on the bottom left of my screen it says I've got 14 marks that's to represent one mark for each day excellent so now I could change this to maybe 28 days and now I see the last 28 days of sales or if I want to do the last 90 days of sales I can do that as well so that's a really neat way to be able to use a level of detail expression to show the last ten days okay in this next example what I want to be able to do is each of my bars so my green bars represent winds my blue represents drawls and my losses and orange represents losses what I want to do is I want to put labels above each of these bars okay so how would we go about doing that alright so the first thing I'm going to do is I'm going to go under the analysis menu I'm gonna go down to Table two totals and I'm going to turn on my column grand totals alright so now we have this big bar here at the bottom and I'm going to right click on that and say well let's move that to the top alright fantastic and then I'm gonna go ahead and let's see I need to go here and I need to do I'm missing the oops sorry missing the wrong object there sorry about that folks am I forgetting here okay let me go here and then I want to check my show header that's the wrong field I am totally bluffing on what I'm supposed to do now so call until those at the top I should be able to hide this oh there we go so now I want to hide click on the bar again choose hide click on the bar one more time and choose hide now all I'm doing here is actually just making some space okay so let's format this and I'm going to get rid of the label as well so let's get rid of that and then lastly I'm going to go ahead and just remove the lines in between the rows so let's get rid of the the grand total lines on both the header and the paint okay so now we've got a bit of a gap up top okay so I've calculated a reference line for my witness okay so I'm gonna put that at zero so let's just go ahead and drag that on to the detail shelf and when I go to my analytics pane and drag on a reference line I can put it across the whole table and I could say base it off of the winds and I want to use a custom value so I'm just gonna say Innes and set I don't want to show a line I don't want it to recalculate and notice how Tablo puts it at the bottom well that's easy enough to fix I'm just going to go ahead and format it and under my alignment I'm gonna stick it at the top there we go so now I can customize this I could I could make it maybe you know 14-point I can make it bold and I could make it green to match the color so now we know which ones are the wins okay so how do I do the same thing for the drawls and the losses then so for the draws reference line if I edit that one you'll see I'm saying if they're the first position meaning if it's Leicester City then just return the W's because I want to be at the end of the green bar all right so let's hit OK and then for my losses we're going to do something similar so if I edit that one I'm just adding up the winds and the drawls so let's put both of those on the detail shelf as well and then let's go back over to the analytics pane and let's add two more reference lines so the first one is going to be for our drawls and we want to customize that we'll call this for all what do we do we call Windsor Winton let's oh it's called trawls and let's say no line and no recalculate excellent so now if I find that line here somewhere let's see if I can hover over it here okay so let's format that let's make it it looks like we're using blue for those and I think I use 16-point is that right I believe and bold and then let's make it right and top there we go so now you can see the trawls are represented right above the draws bar okay so I'm gonna go ahead and remove this shading so let's get rid of this shading on both of these okay and then lastly what I want to do is I want to add one more reference line for my losses table and let's do losses and the computation is going to be custom and I say loss oops losses or just loss or lost something like that nerd losses about that and let's do no line turnoff to recalculate all right and then I just repeat that same process of format the line let's make it 16 and bold and looks like it's using some kind of yellow and turn off my shading and then lastly make it the right and the top okay so there we go so now of course I probably should use a smaller font so so that it all fits so let's make these maybe 12-point there we go that looks better I'll just quickly change all three of these to 12-point tubes helps if I choose the right number and then again one more to 12-point okay so now I have a nice little labels at the top of my top stacked bars so it makes for a really really pretty visualization okay next up all right so in this particular case we want to see which regions are the fastest-growing so how would we go about doing that so normally what we would do is we would take order date and let's say we look at it at the day level and we would take maybe sales where's my sales say I've got a lot of fields in here and we could do something like that come on sales where'd you go and we could maybe make it a running total or something like that but what that doesn't do is it doesn't tell us how long they have been operating for so I'm actually going to create a calculated field I'm going to call it days since first sale and what I'm going to do here is I'm going to I'm going to create a level of detail expression I'm going to fix it at the region level and that's basically saying for each region we want to calculate the minute orderdate so what's the first order de tubes the first order date for each for each region okay and now we want to create a second calculated feels that's days oh let's let's undo that oops sorry let's redo I need to rename this one guy gave this one the wrong name okay so let's copy that I want this is actually the day of the first sale not the days since the first sale and now we want to create another field that's days since the first sale in this case we're going to do a date diff at the day level comparing the day of first sale to the order date alright and the tablet is going to put that down here as a measure I'm just going to drag it up and make it a dimension and then right-click on it make it continuous and I'm going to put that up here instead so now we instead of showing the actual date at the bottom is showing which one has grown the fastest so in other words when I hover over I can see how many days it's taken them to get to you know to where they are so you can see they all started about the same then it looks like the central region took off a bit while the others grew at a pretty steady pace but what you might want to do is you might want to change that a bit you might want to make it a cumulative percent of sales so how long did it take each of them to get to a hundred percent of sales so what I'm going to do is I'm going to just rebuild this again so again I'm going to put my day since first sale I'm gonna put sales in my rose and I'm gonna make this a running total okay so we're back to that same view but now I'm going to edit this I'm going to add a secondary calculation on here so right now this is telling us for each region calculate the running total of sales by day by the number of days since first sale okay great but I want that to be represented as a percentage because I want the total to end up being a hundred percent so I'm gonna add a secondary calculation make a percent of total and I'm going to compute it the same way so there we go so now this second part is saying calculate the percent of total of days since the first sale and there we go so we can see kind of where the ebbs and flows of these of these are okay so that's the cumulative percent of total sales and then lastly the last thing we might want to do is let's go ahead and build that view one more time so I'm gonna pick what's sales on the rows I'm gonna put Jason's for sale on the columns and again this time I'll just make it a running total make it simpler so what about profit okay so now I've got sales in this field here but really I want to be I want the user to be able to pick whether they want to be sales or profit okay so let's create a quick parameter oops not a calculated field we'll do that next so let's go ahead and create a parameter anyways I'm gonna just call it choose a metric in this case I'm gonna just go ahead and do a string and I'm gonna call it sales and I'm just going to do two for now sales or profit but you could add more to that list if you wanted to hit okay and now we have a parameter called choose a metric and parameters don't do anything until you tell tableau what to do with them right so okay so I'm gonna actually edit this table calc and let's let's make it our secondary again I meant to do that and sorry about that so percent of total and then again same way going across okay perfect all right so now what we want to do is we want this metric to swap out based on what metric has chosen from our parameter so what I'm going to do is I'm actually gonna drag this down to save it because that makes it a bit easier for me oh and it just saved it as sales okay so when I just double click in it oh okay so we need to do something different so let's create a calculated field I'm gonna call it metric chose metric value and I'm gonna say if I choose metric equals sales then sales else it and all right so hit okay and now I should have a field called metric value and where to go here metric value so I'm going to put that on the view instead and then I'm just going to add my same table calculation so running total across the table and then add a secondary as a percent of total by for each reach and calculate the percent of total bike day of sale so there we go so now I can swap out between sales and profit and I get a different view so what I would do on the axis in this case so see here how my axis says percent of running total sum of metric value that doesn't really make a whole lot of sense to people so what I'm actually going to do is I'm going to copy this I'm going to copy my title and I'm going to create a new calculated field I'm going to call it my axis title and I'm just gonna paste that in there and I'm gonna give it a better name okay so I'm gonna say percent of I'm going to call it cumulative cumulative percent of total and then I'm going to close that off and I'm gonna say plus my parameter choose a metric yeah it should be double quotes sorry choose metric cumulative percent of total sales okay access title so now we have a new field called access Ted I'm going to drop that on to the rows and then I'm just going to rotate that label and then from there I'm going to hide my field labels for my rows and I'm going to double click on my axis and they get rid of my axis title so there we go so now as I change my metric my my title of my axis dynamically changes as well so very very useful there okay next up can you know formatting all right so what I want to do in this case is how can I color the text of my rows with the profit ratio all right so let's see here so I've got profit ratio in my measures list so let's find profit ratio if I put that on color okay we color the bars by no-mind labels don't get colored interesting alright so how can we fix that well there's a couple things we could do so what I want to do is I basically want to say for each I want to return a constant value here okay so we could see what my max my worst profit ratio here is is -10 right there's a couple ways let me show you a couple ways you can do this I'm gonna start by actually just taking my product subcategory we're actually want to do this I'm gonna put a dummy field in here called average of zero zero point zero because I need to be able to synchronize and you can see that gives me this nice little box here alright so what I want to do here is I'm gonna actually put product subcategory onto the label and let's go ahead and for the most part we're gonna want these to be let's actually change this instead of a bar let's make it a Gantt bar or not let's make it text let's just make it a text field okay there we go so now it's nice and center organizing you see how we're color coding it right so I could do it like that and then dual-axis it and then if I synchronize you'll see I get something like that but that's not really the prettiest thing in the world is it so okay what I could do is I could align these to the left I should be able to align them to the left doesn't look like it's working though okay so that doesn't quite give us the solution we're looking for so let's undo and let's keep going back all right so didn't quite want what we want so really all I want to do is I want to color code these labels so what if I find the window max of my aggregate of my profit ratio okay let's close that come on there we go there we go okay so now you can see we're getting this not wanted to the window min sorry not the window max someone do the window min because I want the lowest value and you can see we've got minus 10% so all of these go down to minus 10% okay so if we now do access those and synchronize we've now got this huge thing of bars and going here on the left right so we should have where did my okay so that's what all these bars represent here but actually what I want these to be is Gantt so let's make those Gantt bars okay great so now I could put my product subcategory on the labels and if I left align those I can then also go to my font option here and match my mark color and then what I would do from there is just make the label I'm sorry make this the size of the Gantt bars really tiny and also make them transparent there you go so and then from here I would hide my header on my top axis and then go ahead and hide the header there so there we go so now we've got some nice labels to do it that way all right so is there another way we could do this well let's see what our other options are here so if we do this for each cell okay so now what happened there is it worked on some of these you'll see it put the value at the end you see the negative ones work well because we left aligned all of those right okay so what we could do in this case is if I want all of the bars on the left so if anything is a positive value then we want it to display as zero okay so let's give this a try so I'm gonna drag this down and save it just to save me a bit of time here I'm gonna call this my profit ratio label and let's edit this calculation okay so let's see here so what we want to say here is when I say if the profit ratio is bigger than zero then zero else give us the window min and so let's see what that does so essentially I'm saying here is what I want is I want these my labels that are hidden behind these bars to actually be to the left of the bar so let's hit apply and there we go just like magic we now have labels on the very left edge of each bar really really like that trick all right that's a fun one all right the next thing I'm going to do is sometimes you'll receive a visualization and you'll be like you know I'd really like to know the quick way that I could rebuild something like this or or I want to see you know there seem to be lots of dimensions and measures and this isn't this particular example is it terribly complex but you know I'm sure we've all seen ones that are much more complex so how could we how could we figure out how somebody built this well it's actually not that hard if you just go up to worksheet menu at the top and you go down to this describe sheet option which hardly anybody ever uses so if you're trying to reverse engineer something go to the describe sheet option and what you're going to see here is you're gonna see how this sheet was built all right so it tells us we've got losses draws and wins for each team broken down by position the color shows details about that the date is filtered on season which only looks at 2015-2016 it tells you what the mark type is tells you stack marks are on it tell it even tells you what's on the rows the columns the filters and the color fantastic so this is a really really great option if you need to be able to reproduce a visualization that somebody else has created so again you go up to worksheet and then describe sheet and you have that nice little option there all right next up we want to look at grid lines okay so sometimes we'll have a visualization I'm just going to go back to superstore I think I can actually close some of these data sources that I don't think I use them in this particular example and I don't think I use for population either I'm just trying to clean this up okay so let's see here what do we want to build all right so let's say that we want to build a slope graph all right so I'm gonna just look at the last two years so let's just do keep only on that and I'm going to look at sales and how it changes that and then I will look at it maybe by let's do something simple let's not do something simple who like simple let's do it by state let's put state on detail and we get a nice busy view like this right and I could even you know on the color shelf I could put some dots on the end all right so we get something like that but what I want to be able to do is well first off I'd like to see these lines color coded right so I'm gonna need to write a calculation that compares 2015 to 2016 or 2014 sales so let's see what I have here so I've got I already have a field called 2015 sales so that's great okay but if I create a calculation I'm just gonna call it you know increase 2015 one I did you say 2015 oh boy bigger than 2014 has a question mark and I could do the sum of 2015 sales is bigger than the sum of sales 2014 and if I do something like that and I put that on color notice how I get this gradual color shading which looks pretty hideous right so first of all how do we overcome that issue okay so what I all I really need to do is just wrap this in an LOD so I want to say for each state I'm sorry fixed on the state level give me the sum of 2015 sales and I compare that to the fixed at the state level of the sum of 2014 sales and then close that one off all right so let's see what that does now okay well notice that it actually moves from my measures up to my dimensions so now if I put that on color nice now I get two colors perfect so my my in this case my orange means increase I want those in the front so I'm just gonna rearrange those and I'm gonna make my throughs green and my false is just gray I don't want to really want any attention paid to the losers okay so great so we've got that but now let's say I want a nice little reference line because ultimately I want a line that kind of drawls down this box here right so let's put a reference line here on the table and wrong spot here I need to actually make this continuous first okay so let me get rid of that and if I put a reference line on on my year and I just set it to a constant of 2014 that's fine and let's make it a nice little dotted line and it okay the problem now is you can see that the dotted line is in front of the is in front of the mark so you see there and see you see as you might it might be a tough let me zoom in a bit here but you can see how they how the reference line is actually in front of the mark so I don't really like that I like the reference lines to be behind so how can I get around that okay well it's really not too hard so first off what I'm gonna do is I'm going to create a calculated field that returns my year so I'm going to call it year I'm just gonna drag this down alright and now I have a field it's probably went down here so let's move that up to my measures and let's make it continuous and then try them we're just going to replace that here okay great and notice how we now have some nice little reference lines in the background right you see then because it's no longer a date field we can tell it to zoom in on those okay so what I can do from this point is I'm actually going to change my years to a decimal and see how that forces the axis to zoom in a bit right so the last thing I need to do is change my default number format my year to be a year with no decimals and no commas and there we go so now I can shrink this view down so if I just shrink this over I now have a nice little slope graph with some lines in the background so the last thing I would do here is format my view and get rid of my access rulers and maybe I can even get rid of my gridlines or let's get rid of my row gridlines and heck let's even just hide the title just to make it nice and clean so there you go so now you can I just did that to make it nice and clear I probably would keep those out there and then I'll get rid of my my zero as well so there it's so now so you can see how much cleaner this looks and if i zoom in again like I did before so let me zoom in here you can see that there's no line in front of these dots so you can use gridlines in that case to make it look a lot cleaner okay let's see the last thing in this particular view is you can see in my tooltip it says 2015 bigger than 2014 Falls well if I don't want my users to see that I'm just going to go to over here on my marks card and I'm going to click on my 20 my feel that's all my color shelf I'm just gonna uncheck this option that says include in tooltip so as soon as I uncheck that that field is now gone for my tooltip for good so very very simple to do so again that's just how they use grid lines instead of reference lines another common thing that people ask about is being able to conditionally format a tooltip okay so let's look at a couple examples here let's just start by product subcategory and we want to look at oh let's say let's we have let's just look at sales something very simple let's make it fit entire view and sort in descending order and what I want to look at is the profit ratio so let's just stick proper ratio in color right but when I hover over you can see that it'd be nice if I could color code my profit ratio right I just want it to be blue if it's positive or orange if it's negative so one thing I could do is I could start by just profit ratio positive and I'm going to just take my profit ratio and I'm gonna say is that bigger than zero no say bigger than or equal to zero and hit ok that's going to return a true false and let's put that on color instead and I'm just gonna put proper ratio on the detail shelf okay so now we can see we just are using the oranges and blues but let's make the let's make the negatives red and the positives like maybe purple or something doesn't really matter or let's make them blue okay so the ones that are red mean that they're not profitable the ones that are blue are profitable so now in my tooltip what I would like is that profit ratio where it says minus 5% to show up in in red and for office machines where its 14% I want that to show up in blue ok so how do we handle that well first off I'm going to go ahead and uncheck my including tooltip for my profit ratio coloring so that it doesn't show that all right so now what I'm gonna do is I'm gonna create two calculated fields so I'm gonna call it positive positive profit ratio I'm going to say if profit ratio positive then return my profit ratio very simple and nothing particularly complicated there and then I'm going to duplicate that field and I noticed I spelled profit ratio wrong which you're positive wrong which is a bit annoying so let me get rid of the extra eye there helps if I spell it right sorry things like that annoy me and now this case I'm going to say profit ratio negative you know I'm gonna say profit ratio oh no sorry wrong that's the wrong field sorry I copy the wrong one so let me delete that I called this my positive profit ratio so I want to duplicate this one sorry I duplicated the wrong field and I call this one my negative profit ratio so I'm gonna say if equal false meaning it's negative then give me also give me the profit ratio so now I've got two fields here right I've got negative profit is your positive profit ratio so let's set the default number format for these two percentage one decimal but I actually like my I like my percentages to have a plus a front or a minus in the back so I'm just going to go to custom number format and update that so now I have two new fields I'm going to throw those on to the tooltip and you can notice how like right here in my positive profit ratio you'll see it says plus 14.2% my proper ratio 14 but my negative part ratio is no and then it reverses when I get to the red one okay so that tells me I'm pretty close to what I need so I'm gonna just click on my tooltip shelf and what I'm going to do is instead of having profit ratio here I'm gonna actually move my I'm gonna cut and paste this one put it there and then I'm going to cut and paste the negative right immediately after it and then just do a tiny bit of cleanup here oops and let's make our product ratio products soap cutter nice and big but now what I can do is for my positive profit ratio I could just clerk out that blue and for my negative profit ratio I could color code that one in red alright so now what happens when we go over our tool tip so office machines is plus 14.2% in blue tables is minus five per two percent in red so that's how you do conditional formatting inside of a tool tip all right two more to show you the first one is how do we share calculate the difference between the 75th and the twenty-fifth interquartile range wow that is a mouthful alright so I actually built an example like this awhile ago but I'm going to show it to you again anyway so let's say we want to look at let's take order date and let's make that at the quarterly level and we know let's let's go ahead and create a calculated field so it's called 75th percentile 7 if percent yes any for the percentile yeah and let me go ahead and try to move this so I can show you what to do here so now if I scroll down here into my fields you'll see I have a percent tile field here so okay this says percentile sales 0.9 so it says it aggregates the calculation returns a percentile okay so I'm gonna click on double click on percentile I'm going to put sales in there and then I want this to be at point point seven five so that would be my 70th percentile all right hit OK and let's duplicate that and do it for my twenty-fifth percentile so you simply change it change the name of the field and then we change this to 0.25 so now we could drag that on to my rows and it will show you the 75th percentile value and what I'm gonna do is I'm gonna take my twenty-fifth percentile and I'm gonna drop it on top of my 75th percentile to create a combined axis view so now we get something like that or maybe we can even change this let's say we don't like the 75th we want to know what the 50th percentile is which is really the median so let's make this point five okay and I'll change my title to make sure when you guys look at this later you understand what it's doing so now we've got we can see the difference between these two but I might want to look at this on a quarterly basis so what I'm going to do now is I'm gonna actually duplicate my measure values field and I get two line charts great on the first one I'm going to change this one to circles so I get dots for each one on the second one I'll leave it as a line but I'm gonna move measure names to my path shelf okay so that didn't quite work right because we want to actually connect top to bottom so let's change this to discreet and now we get the view that we're looking for okay so from here I'm going to make this dual axis synchronize and I'm gonna move my lines to the back there we go so now we can see quarter by quarter I can see the difference between my 20 my 75th and my 50th percentile of sales so that can be a super handy view if that's the type of metric that you're interested in the last one I have to show you is sometimes you want to be able to compare an individual month to the historical monthly average with a level of detail expression okay so what does that mean so let's start by looking at let's see what order date and then let's make this month right so let's do month there oops four months let's do this month and what I could do is let's say let's move month to the detail shelf and we get you know basically one value there and let's look at sales let's put sales in the rows and let's make these circles okay so essentially what we're looking at now is for each year we can yeah for each you know I've got these yeah so I wonder I want to basically calculate within for each month what is a historic monthly average okay so let's do that so my monthly average across years so in this case what I want to do is I want to write a level of detail expression that says okay fix me on the month level so what I'm going to do is let me close this I'm actually drag this into the view because that tells me it's the date part of the month so I want the name of the month and I want to know the sales the sum of sales so that's going to say for each month give me the sum of sales actually I want to do this for each month and here so let's make this a date trunk this should work let's try it I might be messing up my example here and then I want to take the average of all of that or no we should be able to do it this way let's do date date part let's change this back date part and then let's make this the average sales okay so this is saying for each month give me the average sales all right so that would be equivalent so you I'm here to elect Payne and dropping on an average line for each pain oops or not for each pain we want to do it for each cell sorry about that Purcell alright and if we show the value of that let's edit that one more time let's go ahead and show the value on there instead so now we've got 3 3 3 51 282 so now if I look at my monthly average sales where to go across the years and put that on detail um we should see ok so might and see how my monthly average sales is only returning 1 8 4 6 ok so I've done something wrong there so I think what I want to do is I want to I want to sum these up let's see what happens here if I sum these up and then take the average of that so it's going to break that feel which is ok and now we can see the monthly average is 128 4 7 24 okay so I didn't quite do this right so let me see here let's make this a date drunk and hit applying so this is where it's a lot of trial and error ok so so now this is telling me notice what it's doing now is it's giving me the same value for each month and year alright so how do I get back to my 351 ok I'm actually totally blanking on how I do this one before let's do the average like this let's try it like this sorry folks you think I practiced these ahead of time wouldn't ya ok so that's not quite working so why don't I just show you a different example so maybe with let I'm just going to completely change the topic here and let's say what is the total value of the top seller in each month so what I mean by this is let's look at for example let's look at let's look at a by stage so let's like okay so let me change this okay for each state what is the total value of the top seller in each month okay so basically what I want to I'm kind of creating a table that looks like this so in other words if I if I have Alabama and I drag order date in at the month level no at the detail level let's do it like this okay each day and if I put row ID in here and add all and then I put sales in here essentially what I want to do is so on Alabama for January the third the highest sale was 698 so that's the one I want to count when I get down to the next day there was only one sale so I want to count 529 when I get to the 22nd there were three sales or three items sold so I want to keep the 48 I'm sorry forty-five 85 okay so let's create a calculated field I'll call it largest sale per day and what I want to do here is I want to I want to fix this at the state and order date level and I want to get my max sale all right so I should have my largest sale per day and I drag that onto the view and notice how in the first one I get 698 in both records okay so now I could take row ID out love the view I can't even take order date out of the view and now I know the total value of the largest sale so if I put this up here I know let me put that on detail I can sort this and now I know that Cal the individual largest sale per day is always 1.1 million well a neat thing to do here then is to create a nice little calculation so let's say largest sale contribution so this is where kind of the analytical process works here so I want to say how much did the largest sale per day how does that compare to the overall sales per day right so now I could take my our just sale per day contribution let's default the number format for that to a percentage and let's drag that on to color and now we can see let me make it a two color palette we probably want to reverse this because they're the reason you'd want to highlight the ones that are you'd want the so this is telling you in main for example that the single largest seller per day accounts for ninety seven percent of your sales well that can't be good because then that means there's there's less deferred that it's a less diversified portfolio okay so that brings us to the top of the hour and thank you very much hopefully I don't know if there's any more questions I'll check on here hopefully people can still can still hear me and yeah thank you very much I appreciate you guys listening and I will have the workbook and the video up on my blog and in a short amount of time so just check over on vizwiz com probably in the next couple of hours and I'll have it up for you so thank you very much everybody and have a great day

Channel: Andy Kriebel

Views: 12,787

Rating: 4.9272728 out of 5

Keywords: tableau tip tuesday, webinar, the information lab, zen master, tips, tricks

Id: uzmceF3_k7M

Length: 52min 10sec (3130 seconds)

Published: Wed Nov 01 2017