Tips and Tricks from a Tableau Jedi

welcome to this recording of Jedi tips and tricks for tableau conference on tour 2014 my name is Alan Eldridge on the strategic sales consultant for tableau software based in Melbourne looking after our customers in Australia the purpose of this session is to present a number of different scenarios that are going to help equip you to become a tableau Jedi that is somebody who has an intimate understanding of tableau and how to use all of the features and capabilities to create and creative and elegant solutions the session isn't about showing you how to do incredibly hard table calculations or create crazy complicated visualizations often the topics that we cover in these sessions are actually quite subtle techniques designed to help you take advantage of features and capabilities of the product to make developing solutions easier so for example there may be scenarios where you know you could solve the problem by writing a bunch of calculations or leveraging some complex table calx and so on but if you think a little out of the box there are other ways that we can leverage features and capabilities to make solving those problems much simpler simpler and oftentimes much more elegant historically the topics that we've covered in these sessions have often been workarounds around limitations of the product or maybe ways to extend the product beyond you know what you might be able to do natively out-of-the-box and each year coming up with topics to present in this session becomes more and more difficult one of the main reasons for this is because our developers do a fantastic job of increasing the capability of our product with each revision so things which historically might have required us to write calculations or use table calculations could now simply be a right-click putting that same capability the ability to answer those same complex calculations in the hands of everybody and you know giving them a touch this Jedi capability so what we want to do today is to work through a number of examples and at the end of it hopefully leave you with possibly a couple of techniques or maybe a few ideas of how to go about creating solutions and take those back to your day job so let's have a look at our first scenario here so I'm just going to come out of presentation mode and for this first scenario let's building to this little gradually so first of all what we'd like to do is to create a visualization that shows us the top and let's say the top five subcategories based on the total volume of sales so that's pretty straightforward we'll take our product subcategory we'll take our sales here's our visualization now if we want the top of these it's always good practice to just sort this information and here it is now we could just eyeball this visually here but if we wanted to put that in as a filter for example we want the top five in here then we could simply take our subcategory hold down the control key drag it over to the filters to duplicate it in here and we could choose to do a top style filter in here and we'll choose the top five and click OK so there it is there's our top five subcategories for all of our number based on on total volume of sales that's great now what if we were to take this problem and say well okay that's great but I'd like to actually know what are the top five subcategories for each year and how does that this change you're over here well I have my auto date over here I could very easily grab this and just bring this across here and now I've got my years with my subcategories in here but if I look at this this really doesn't look right you can see here that the numbers are out of sequence in here and if we take a closer look you'll notice that it's actually just the same five subcategories repeated in the same order inside the years now the reason for this is because this is now a nested dimension over here all right so let's simplify this down a little bit see if we can understand what's going on so let's take the filters off and even if we take that off you see that these things are not sorted in order even though we have a sort being applied at the top up here we can see that and again if we take a closer look what we can see is that the subcategories are actually just repeated in the same sequence within each of the years if at the years off you can see that it's actually the same sequence that we looked at before so that the fields are actually still being sorted by the overall total and then they're just being split down by the individual years and here this isn't really what we want to be able to do we need to do a nested sort for those of you who've been around for any length of time with tableau may know that the approach that we've taken in the past there's a couple of different ways to do it but the one that most commonly gets used is to use the index table calculation so we create a calculated field we'd use index we'd bring it into the visualization and then we would have to set the calculation controls for that table calculation in nested scenarios it gets a little confusing so this is one of those examples of where something that's previously been very difficult is now actually very easy because one of the things that we've done is in tableau version 8.1 we now have a simple table calculation for rank here so I can just choose rank and now you can see we've got a calculation happening over here it's still running all the way to 70 and so what it's actually doing is doing a rank across the entire table but we can just grab this and tell it that we want to compute using the product subcategory in here now it just rains from from 1 through to 17 in here and for each year it will repeat and you can see that we have these different ranks in order here now I would like to just sort this but you can see here because rank is a table calculation I don't get the same ranking button or the same sorting button that we get over here and I could use this hover menu to apply a static sort on this but if the data changed over time we'd need to reapply this and it's not really a great solution so what I what I can do is using this ranking function now leverage a behavior that we know of tableau when we work with discrete numeric fields so I can take this field over here and I'm going to make a couple of changes and you will have a couple of steps in the middle we just need to ignore those if I change this to be a discreet data element instead of continuous you can see the color of the pill changes to blue indicating it's discrete and if I just move that down in front of the subcategory pill we can see that now here's our rank of sales and because it's a discreet numeric tablet will automatically sort it into ascending order for us in here so you can now see that here's our table being sorted really nicely in that rank order and we can clean this up a little bit we can right click and say let's don't show the header so we take the number away and if we format the table we could come in here and just bump up the level for lines and now we pretty much have the table looking as it did before but it's sorting in that correct order for us in here and you'll see that in each year the sequence is different that's because it's doing a true ranking for us in here so that's great but what this shows us so far well so before we move on let's take this one step further maybe we still want to be able to restrict this down to just the top and I just want to see the top five products inside each year well again we could leverage the fact that we have this ranking field over here why don't we just use that as our filter so I can drag this and drop it over here on the filter shelf but because it's discrete you can see we get a discrete selector over here so I could you know come in here and just manually choose one through five likes obviously not a great solution in here and what would be better is if I can just take this version of the pill so over here we've got it working as a discrete element over here I could pick this pill up and I could use that as a continuous solution now you can see we get a different kind of filter and I could change this disable as to an at most style of filter and I'll surface the quick filter for that and now I've got this little visual utility on the side over here that allows me to just very easily change the filter and what we're filtering on is the rank so what I'm now looking at is the top five being brought through in here very easy for us to do that we haven't had to create any calculation it's a very neat way of doing it all right so this solution here is great in that it allows me to see what are the top end sub categories in each one of our years in here what it doesn't do a great job of though is allowing us to understand how those trends change over time and it would you know certainly become far more complicated if we changed our granularity to anything lower than a year we'd end up with a very long table it'd be really hard to understand what was going on so I'm going to just duplicate this and we'll make some changes to this visualization here and I'm going to pause the design here because I want to turn this into something that we call a bump chart or a parallel coordinates chart now in order to do that I want to have my ranked Sun of solar by years across the top so my time dimension is going to run across so we can look at a trend I don't need the absolute value of my sum of sales but I'm going to take my sum of sales as a discrete element and put it on the rows we'll take our subcategory we'll put it on the detail shelf and we can take this one off now if I click OK we end up with this kind of view here as I said it's called a bump chart or parallel coordinates plot now what it's basically showing me is this is the rank each line represents an individual product subcategory so this is paper and you can see that paper has been very very consistent it is the 12th ranked product across all of our years in here there's a couple of little refinements that we might make to this visualization here the first is our rank is really running in the wrong direction so rank number one is actually our best product we'd really like that to be at the top so that's a pretty simple change that we could make where we just edit our axis and we all reverse the flow of this axis in here so it now runs from top to bottom so our highest ranked products are now up at the top a lower strength product down at the bottom down here so in 2011 for example the number-one-ranked product was office machines in 2014 it was binder and binder accessories like so it's a little hard for us to tell what's going on with this at the moment it's not too bad this is just a looking at it in terms of years in here but the great thing about this bump chart as it makes it possible for us to view this at a much lower level of granularity so if I change this out to look at this data plotted across quarters all of a sudden we now get a much busier kind of view being displayed so how can we make how can we make some sense out of this particular visualization well at the moment we've got all of these subcategories being displayed here maybe I could take my subcategory instead of just drawing a line maybe we'll use that to plot the labels so that's putting some of the labels onto the chart it's not great some of these lines are still unlabeled and then we have the tooltip so we can always hover over it and understand that but it really isn't terribly discoverable for us in here another approach might be to use subcategory on color for example now this allows me to actually start to see some of the trends and patterns and if you again work with tableau for long enough you'll probably know that we have the ability to click on the legend over here and start to use this highlighting technique and we're probably just on the edge of getting to the point of pushing it with color there are 17 values in here really anything over 20 is probably too many and I think even that's starting to push it to some extent so it's difficult to sort of tell them apart in here so what can we do to make this a little easier to read so we've got this concept of highlighting if we could just make this a little bit more immediate in here that would actually be really helpful in terms of discoverability for our end users certainly those who are new to tableau well one of the things that we could possibly do is leverage a capability called actions and there's a type of action called a highlight action now we could take this and say well when I click on something any when I select an object we could highlight this whole line over here so if I click on something will highlight the whole line that's great and maybe what we could even do is take the subcategory instead of using color maybe we'll put that on the label and we'll tell the label to just show the the value for the highlighted so if I choose that you can see here that it's showing me whatever it is that I've got highlighted in here well maybe that that's great but it requires me to know that I have to click on things to make something happen there's another way of triggering these actions and that is to use hover action in here so I'm going to change this and have a hover action happening so let me just check that I went into the right let's add a highlight on hover this time still doing the same thing of the sub product subcategory so we get the whole line rather than just a point and what we can now see is if I just hover now the tooltips a little troublesome at this stage here so let's just take the tooltip away and now as I hover over these lines you can see I get the line being highlighted so it's much more immediate friend end user and just really helps increase the discoverability and prior to tableau version eight we're always a little bit hesitant about recommending the use of hover actions because certainly when you took the dashboard and published it to tableau server each time you move the mouse potentially incur a round-trip to the server to redraw the chart with version eight and beyond we've started to use more heavily the concept of browser side rendering so these have become far more responsive and far less of a load on tableau server so we can start to introduce them I'm not suggesting that you use them all the time but they really are now something that we can start to think about using for these kind of scenarios in here all right so let's move on to our second scenario now this time I want to look at some information across times I'd like to look at my sales across time and the particular question that I want to ask this time is that I'd like to be able to look at my current year year-to-date values and compare them against last year's year-to-date values and I really just want to track them through to the current month that we're in so let's start looking at this I've got my sales data in here and I have my order date so we'll bring that out let's look at the year and we'll drill down to the quarter and down to the month level so you can see here that we have three and a bit in fact it's running us up to the current date which is the first of April so you can see here that we have three and a bit years worth of data now we want to look at year-to-date values so that's pretty straightforward we can create a quick table calculation into year-to-date that gives us our sawtooth kind of lines happening in here it's flat on April because April has just started and but what I'd like to be able to see is comparing this year versus last year well I can kind of do that on this chart here maybe one reaction would be or why don't you just take here and put it on the color and now we can easily compare the current year not just with the current year in the previous year but against all years in here and so that might be a proposal that we can put back but you know I have a pedantic customer there saying that's not what I want to see well maybe we could take our year and we could show a quick filter so we just change this to say I only want to see 2013 and 2014 again it's showing me that information but the charts getting a little bit skewed because the Green Line from last year continues all the way through and I really just want to see the current and up to the current month in here right so at this point here it becomes a little bit complicated particularly if we wanted to create something dynamic that we don't have to keep changing every time the numbers tick over whether the data refreshes so what I'd like to be able to do here is you know well the one approach would be to create some cow Galatians and have some logic in there that says well if it's this year and last year and maybe have some parameters and work out what month it is and really start to filter the data at that point because we have to create some complex calculation logic well I'd like to show a slightly different approach in here so I'm just going to roll us back a little bit to this view here where we have the data plotted across time now as I point out we can do this with a calculation but another way arguably slightly more elegant and we can use blending to solve this particular problem so what I'd like to do here is to actually duplicate this particular data source so here's a copy of superstore and I'm just going to rename this and I'm going to call this this is my here is my time and my shifted data cuz what I want to be able to do is have one set of data shifted forward a year now you can see here that the two data sets are actually linking based on order date and that's fantastic I could take my sales down here let me just rename this and this is going to be my sales shifted as well so I could take that and I could plot that on here and if I turn this into a where are we is this guy here yep take this and do a quick table calculation which is a year-to-date total you'll see that the two lines are basically on top of each other I can't see the one or the other we could drag this down and you'll see that one's on top so they're identical at this point but we really want to shift one by a year we can leverage this concept of blending to do this we're going to take the original order date and I'm going to rename this and I'm going to call this the original order day now as soon as I do that the blending breaks you can see here that the blending has stopped working at this point on the time series but if I take my original order date over here and I create a calculated field and all I want to do here is to create a date add and I'm going to add one year to my original order date and I'm going to call this order date giving it the name of the previous field now when I do this tableau automatically links based on type and name so we're back to having these two datasets linking together I have an effective blend but if you look at what's happened the green line has been shifted forward one year so in each one of these panes I'm now looking at current year and previous year because they're shifter they're offset by a year so we're really starting to see what we want now more importantly than that I'm now starting to see that happening up here in the end year where I've got last year and this year starting to happen so I could change these values in here I could take this and say well the green value is let's edit that alias and call that last year and the blue one will grab this and we'll edit its alias and call that this year so here are my two values I could potentially take that measure names and put it on labels in here now all I have to do is to create a quick filter on the year and in this case I could easily turn it into a slider and now I could just flip backwards and forwards across the years and in both cases we're now starting to get that behavior that we're looking for showing as the year to date and it's actually filtering based on what we want really simple no need to do any well a very simple calculation just to timeshift that field but the power of blending because it works as a left outer join means that the values for last year stopped at April because that's the last month that we have in our primary data source so a neat kind of approach that we can use to overcome the need for doing some really complicated time series calculations now if you are concerned that what we're doing here is duplicating the data we're really not you can see that we use an extract here by just duplicating that data connection I haven't created a second copy of the data extract I've just created another set of metadata pointing to that data extract in here so it's not duplicating the size and it's not increasing the number of cycles that we would need to do a data refresh it's just giving me another semantic layer view of that data so I need kind of approach using a self blend alright let's move on to our third scenario now now as you can see here I've started to build this solution already and what we're looking at here is my subcategories and the sales associated with them and I have a time slider over here on the side so I can start to change this and when I let go the screen redraws but you know one of the things that is you know less than great about this is I can see here that you know it runs up to a little under nine hundred thousand over here at the top mark over here when I move this you can see that the screen Gray's out and when I let it go it redraws okay now that the top mark is five hundred and fifty that's great but I really sort of lose that I understanding of what was the original value and kind of what proportion am I now of that original value so between sort of the graying out of the screen and then sort of the snapping and redrawing of the charts it's and losing of that original context it would be great if we could have some of those capabilities added in so again one of the new features that that's come into the product lately is this concept of being able to do a dynamic redrawing of charts when we work with dynamic feel with continuous fields so at the moment what you're looking at is a single bar over here which is the grand total but if I take my order date and just bring that down on to the level of detail I choose the continuous order date and choose ok what you can see is that that bar has now been broken up into a whole bunch of smaller bars each one of these representing an individual order date in here now we could clean up this a little bit by coming to the color over here and turning off those borders so it looks kind of like we had before but the great thing about this is that now as I start to move this you can see that the chart is able to dynamically update itself as I move the filter in here so this gives me a great way of really having a feeling so I okay I have to move the filter this far over to get to sort of halfway reduced against the values that we had before now when I let go it's still kind of the snaps and redraws in here will tackle that bit in a second but this is a great way of being able to to create this kind of dynamic data redraw now as I said it only works if we're filtering against a continuous element if they've taken this and chosen to display this as a discrete when I change this we still have that same old behavior here so the trick here is to really make sure that we use that that continuous value in here so we can get this animation effect terrific now what I also like to be able to do is have in this visualization here as I move it I'd still like to have some kind of visual representation to show me what the original value are so I have that contextual understanding of what the new value is compared to where it started so again a great way of doing this would be to use blending so once again I'm going to take my superstore and I'm simply going to duplicate it I'm duplicating it again because I just don't want to use that shifted value down here remember that's time shifted so I'm going to take this second value over here and then from it you can see it's linking on date and subcategory I'm going to just take my sales value over here and from my secondary data source drag it and we're going to create a dual access chart now when I drop this here things go a little bit crazy turns everything into circle plots in here but I can fix that by just saying well we actually want to make sure that everything is a bar and I want to make sure with the dual axis here that we're going to synchronize these axes together and we'll we'll take this measure names off the color in here so what I have am i to and what I'd like to do on my second axis over here is I'll choose a color that's just a little bit fainter than the original so my secondary axis is on top at the moment so let's just make sure that we send that to the back and now as I start to move this notice they're all still moving together in sequence so what I want to be able to do here is I just want to make sure that on my secondary data element that I brought in we're just going to take the order date off what that means is it'll no longer respond to this change over here so if I now start to pull this down you can see here that we're slowly uncovering the bar that was underneath it originally and if I let go now you can see here that things are changing a little bit because we've got a sort on this subcategory over here if I just clear that sort because what it's doing is it's just changing the sort order based on their new values in here but if I just change this you can see things are moving when I let go it stays where it was and I still have that light gray bar indicating what the previous value was so it's a great way to sort of provide this contextual understanding or was the original value and what is the new value in here and the way that we do that is by doing a blend and then selectively decoupling on one of the axes the ability for this dynamic filter to work in here let's move on to our next scenario now and this is a scenario that's relatively common for those of us who work outside those areas where there's really good polygons definition built into tableau it happens quite commonly here in Australia so the plot that we're looking at here at the moment apart from being wildly Technicolor and these are Commonwealth electoral divisions of Australia and I've downloaded this information from the Australian Bureau of Statistics and run it through a little process which I use Alteryx to do to produce effectively a series of vector points so it's it's an outline of each one of these polygons in here and by then taking that and displaying polygon marks and using these point IDs and polygon IDs and it's kind of like a really big complicated kids point-to-point puzzle so you draw around the outside of the polygon like so and then fill it in with the color fantastic now I'm often people say well I'd like to actually label these polygons so I can certainly hover over them and get information about it so this is Ling Ari over here this is Jack and this is O'Connor but I'd like to have you know either the name of the electoral division or maybe some metric some value being associated with it I want to plot that onto the polygon now the problem with that I've got my my electoral division name here is it when I drag it out here you can see that when we have a polygon marker I can't drop it on the label shelf it's not highlighted because you can't label a polygon so how are we going to put a label and ideally what we want to do is put a label in the middle of the polygon to some extent so how can we do this well the way that we're working with the data at the moment I'm using latitude and longitude as dimensions so that means that they don't aggregate as you can see up here but one of the really powerful things about tableau is we can actually change whether we use a data element as a dimension or as an measure and we can do that dynamically on the fly so I can come up here and start to change this logic and say well I want to change these from being just a dimension where we will automatically plot a data point for each value in here but change these to be a measure and I'm going to choose average as the aggregation and you'll see here that it changes the to the average of the longitude and to the average of the latitude now it doesn't change the visualization at this point in any conceivable way and that's because we still if we look at our level of detail it's down to the individual point so the average of one point is going to be that point but what this now means is we can start to get a little creative because I can just duplicate this field sideways to get a second copy of this particular chart takes a second to redraw there it is and then on this second chart over here change this from being a polygon to being a circle now you can start to see we get these data plots happening here now this is happening at the polygon ID level we're going to take polygons off so now I'm just getting ups and I've made some mistakes here because I did that for the all let me come back and make sure that I pick my second map to make these changes so I'm going to change this second map to be a circle sorry my mistake so we'll change one of these to be a circle the other one still remains as a polygon and if I take that off what I've essentially now got is a point which is going to be plotted at the average of all of the latitudes and longitudes so if we think about this green area up here this is direct and we have a corresponding plot over here which is being placed at the average latitude and longitude based on all of the vertices in that particular chart now because this is a circle we can actually label a circle so I can put that there we can start to plot the labels in here and I can now make these circles quite small I can change my label so that it sits right on the middle of the circle instead of under like so and I can even take my circle and make it transparent so we'll make it completely transparent so there's no mark actually being plotted and we'll just see the actual value being displayed like so fantastic and I have to turn the borders off as well so they go away like so so now I can take this and simply say I want to turn this into a dual axis chart and it allows me to combine those two things together again just need to change this behavior to take measure names off and hey presto there is our chart being displayed and that's great we're now able to combine these two things together and you'll see that only some of these values have been displayed at the moment that's because they're being suppressed because I don't have allow overlapping marks I don't have that turned on on the label um but it's not perfect it's not great because if we have a look at this value for durak over here you can see the names right up here on the side and the reason for that is because if you look at this kind of polygon there are only very few vertices on these sort of relatively straight lines in the middle here but the coast line for sort of Northwestern Australia is very very complicated lots of islands and so on and so there are far more vertices over here than there are on the right-hand side and on the bottom so the labels being pulled to the top so maybe average is not the best way to go about doing this so another way that I think gives us a much better fit in here is to simply find the midpoint in here so if we find the minimum longitude and the maximum longitude in here and we divide by two to get the the midpoint in here maybe that'll give us a better result so what I've actually got here is some calculations that do precisely that I just take the maximum of the latitude and the minimum of the latitude add them together divided by two that gives me the midpoint in here so I'm just going to pause this display here and we'll take our latitude and we'll just replace these values instead of the average latitude we're going to use this calculation and we'll put this one up here as well and if I redraw comes back and I think we get a much better fit for these surgery keys now sort of smack bang in the middle of this over here we get a much better fit for the way these things are working now again not perfect we can see this is lingering over here and if I have our lingo is actually this orange bit here now the reason it's being skewed like this is because of our co costs and killing Islands and Christmas island out here in this case here we can just exclude those and similarly we're having the same problem over here on the East Coast with Sydney being pulled out probably by I think this is Norfolk Island over here so again we just exclude that and we're now getting these and so you know it's not perfect it's still susceptible to some of these skewing kind of effects in here but I think gives us a much better solution and again if we were to start to zoom in on let's say metropolitan Melbourne down here it's really going to do a reasonable job of trying to fit these labels in here now they don't have to be the name they could be some kind of numeric value being reported at this point all right let's move on to our next scenario now for this one I've got a predefined dashboard you can see at the top we've got a bar chart and down below we've got a map and it's been driven they're both linked to this time slider and so the time slider is is good commands it's dynamic its interactive it allows me to change this but one of the challenges is a couple of things about this it could be better right first of all it's very difficult for me to select you know an individual week or an individual month particularly if it spans across a lengthy period of time so the ability for me to take this and hone this in on a specific period and then maybe even sort of shift that and compare period one period two together it's very difficult and imprecise kind of selection tool in here the second thing is that there's absolutely no context around what are the values for this particular area of time in here so wouldn't it be nice if we had a better way of you know a way to create a slightly more insightful kind of time selected I don't get me wrong that the the time slider object up here is a great selector but there are times when maybe we would like to have a slightly different kind of approach so for this I'm actually going to try and create some sheets and use them on this dashboard to build another way of looking at our hour time solution here so I'm going to create a new sheet and I am going to call this my ears and I've got my superstore data over here I'm going to pick up my order date and plop it over here so I get a list of years that are being shown in here and for this we'll use our profit value in here so I'm going to take profit and I'd like to see this as a highlight table like so maybe I'll spin this back around so it runs down I'll come up here I don't need the field headings like so I just really want a simple little table being displayed like this so each one of my years being displayed and I can see the the sum of profit fantastic so I could use that same kind of approach so I could take a year and I could duplicate this sheet and create month so this time instead of showing year I just show the months so here are my months down the side maybe you need to widen this out a little bit so here's my months being displayed and this one shows me my years in here terrific now I could create a third one of these and in this case here we want to get down to being able to show an individual month so we're going to look at the day level in here now when we get to this it'd be nice to have a kind of little calendar control so how does the calendar look well a calendar has the the day of the week across the top and it has the weeks down the side so we'll take our order date and we'll pop this in here and we'll put our week number down the site now at the moment it's showing us a lot of information in here so I'm just going to quickly come over here let's start to assemble a couple of these bits and pieces in here so I'm going to have my year let's put that in here like so in fact what I'll actually does put a horizontal layout container and I'm going to put my year in that horizontal layout container so that I can put my month next to it so these two guys right next to one I'll give myself a little bit more space so that we can start to see what's going on now you can choose to lay these adding in a variety of different ways almost there there we go that looks good to use my year and my month side-by-side and I'm going to have my day below it like so now let me just hide that we don't need that so here's my years my months and my days down here and we can start to use these as filters so I could say I want to use the year as a filter wipe this out so we can drink it lose some of those scroll bars let's grab this guy and say let's fit with there we go and okay so I can select on a year and it's going to filter all of these objects to just be that particular year similarly with month we could say I want to use this one as a filter as well so then I'll choose let's say June and now you can see our day is now starting to look a little bit more like a calendar so let's jump back in here and refine this out a little bit so we generally don't really need to see the week so I can take this value over here and we'll turn off showing the header and then I can take this and let's just format this field up here and there's a feature in tableau that allows us to just pick the first letter so we can choose that and we'll get the alignment to be centered for us so we can see you know the the period of the week and what I might actually do is just take my order date and here on the text what I'd like to be able to see is the actual day so one bubble bobbler like that and if I the last thing I need is my profit and we'll look at that like this can is just a little messed up at the moment so we'll get weeks weekdays days on the label and the sum of profit doesn't need to be on the label just on the color like so there it is again what we might do with our label is just ensure that we Center it so starts to look like you know so now we're starting to get some context around this particular display now I can sort of grab down a column or I can click on a day I don't really have anything that allows me to grab a whole row though so maybe one of the things that I'd like to be able to do here is to just create a calculated field and I'm going to create a simple calculated field with some subtle carrot in here so it's going to be my week label and what I can do is take that week label and pop it in over here so we now get this little carrot being displayed we can hide that field label we don't need to see that and now here's that that carrot being displaced I can click on that to be able to highlight a whole row and I could fiddle around with the format in here to change the that the line markers like that the row separators and so on but the one thing that's really annoying to me here is that I get this a little sort I con popping up in here and I really don't want that I don't want to be able to to sort across this in here now what there's a neat little trick that we can use at this point here which is to and we saw this happening earlier when we were talking about ranking and the trick is that we understand that sorting this this kind of automatic sorting where with the little icon being displayed here doesn't appear when we're working with a table calculation so I can just take my sum of profit over here and I could pretty much use whatever table calculation I want it in here I'm just going to use a running total it's really matter too much which one you use because we're still doing this at the level of the day so that the running total is actually just going to be the running total of one day so if I choose that it's really changed that the values being displayed at all but what it does mean is that that little sort I con is no longer being displayed because it doesn't appear when we're working with the table calculations a neat little trick that we can leverage there I might just want to again on this worksheet clean up some of these tooltips and now if I come back over here I can take this and I could say let's be sure that we fit the width so here's my calendar and now when I change from month to month you'll see the calendar is updating accordingly showing me the values for that particular week and then if I selected down here then again I would be able to take this and say I want to use this as a filter now the problem with that is all of a sudden you notice our year and month collapse when we do this because there's only one year and one month that meets this particular criteria over here we don't really want that to happen we want this to drive the rest of the visualization but not these so we can come up here and change some of these actions these are these automatically generated actions so from day we can tell this that we don't want it to target the year and the month sheet we don't want those to be affected when we change something on day I can click OK and now we can pick a month over here and then I can pick a row or I can pick a column like so or I can click on an individual day and the body of the worksheet is being updated over here but not these other sheets over here so I could look at the whole year I could look at okay so again you know some of these things are not going to be great in here what we really want to be able to do is ensure that over here on our actions when we do here when I change something I just want to when I clear the selection I don't want to show all values I just want to leave the filter as it was previously and similarly for month over here we'll leave the filter as it was and we might also want to just check to make sure that we do it for a single select only otherwise things get messed up in here as well so what we can now do is say right I want to look at 2013 I'm going to choose April I get this in here if I then uncheck April I don't have to worry about that I can come back over here then I can choose this particular month or maybe I'll pick January all right so this gives us a another way of creating a more interesting kind of visualization now as I said you know there's nothing wrong with the slider up here it's a perfectly fine way of selecting it but with this approach here we actually get to deliver the ability to target specific days or weeks or certain days and we have much more contextual information being presented to the end-user when we look at this okay so let's move on to our final scenario just a quick one this one so we have a situation here where I am looking at my product subcategories and I have a table calculation on my son of sales where I have a where's my quick table calculation we're doing a percent of total calculation in here and you can see here that they're being plotted out I put a grand total in here in fact let's just turn it that total off and I'm just going to sort these so we can see the data being displayed now I want you to keep an eye on the value for office machines up here at 13 so I've got a filter over here for these subcategories and if I start to turn things off so if I turn off bookcases watch what happens for office machines in here 13% if I turn that off notice a change it's 13.9 15% now obviously that makes sense because there are less categories so that particular value is now going to be that particular category is now taking up proportionally more of the total so what we're seeing here is is actually technically correct but sometimes customers ask us I want to be able to just filter this down to look at a subset of my subcategories but I want to be able to see the true percentage value now I know that it's not going to add up to 100 I understand all the risks and implications around that but as I turn these things off I don't want this number to change away from 13 how can I do that now this is a little challenging because if you understand the way that tableau works with a quick filter and when we do a quick filter we're actually removing those values from the result set that comes back from the data source and when I change when I turn off bookcases those records don't actually come back so I never really know what that value is and so I can't use a quick filter to do this kind of calculation so we'll take that away however and if we understand a little let's take that for a little further and say well okay what we really need to be able to do is remove some of these things and not have them affect the calculation now we can do that by selecting a bunch of data items over here like so now I can say keep only or exclude or if I right click on them there's another option if I pick one of these guys here I can say I can either exclude which means I get rid of it or it can hide it now if I hide this watch what happens to the value of 13 up here I choose chairs and chair mats and I hide that it disappears but the numbers don't change so it's doing a post query filter it's hiding the value in here so that's great but it really requires us to be able to right-click and do hide or you know show that hidden data to bring it back again in here I really like that idea of the quick filter but I want it to be able to work post a query so we need to do a calculation we just do it we need to work with something that happens post query what do we know that happens at that time table calculations are done on the result set that comes back from the underlying database so if we could somehow make these values over here into a table calculation maybe we could do something with that well as it turns out there is actually a way to do that I put this under the category of things that you'd probably never discover on your own but once you're taught this technique it's actually really powerful now broadly speaking we refer to this as a as a dynamic hide and what I'm going to do is create a calculated field and it's a little bit complicated so let's call this my post query subcategory like so and I need to turn this into a table calculation so the way that we can do this is to we use this calculation earlier on in this session we use a calculation called lookup which so lookup basically says look in a particular direction a certain number of steps so the direction is that is the compute using in the number of steps as part of this so I want to look up in this case I want to look up the product subcategory but I want to look up zero spaces away now this isn't working because this needs to be an aggregation because it's a string we can just use the attribute aggregation function in here I'm going to look up the sub category zero spaces I which is basically saying look up your own particular value in here so take a look at yourself I'm going to click OK and I'm going to just pause my calculation let's move the product subcategory down on to the level of detail we need to do that because we still need the product subcategory to define how we want this to happen but now I have my post query subcategory on my rows like so and if I click go so I still end up with the same kind of view and if I select all of these data values in here I can tell it that I want to sort the minute so the numbers are all still the same as we had before but now if I take this object and say show a quick filter well I've now what I'm now filtering on is the table calculation rather than the original field and now if I start to take these fields out notice that office machines is not changing so we're getting the behavior that we want it and the reason for that is because this field this post query subcategory is evaluated after we go to the database and then what we're doing is effectively hiding these values we've already computed the percentage values and now we're simply choosing to display or hide elements in there so I can get that behavior that we were looking at before so that's it that's our our session for today thanks very much for watching this recording I hope that you were at the session at our customer conference I hope the session has given you a few tricks and techniques to take away and hopefully make better visualizations with tableau in your day-to-day work and help you take a few more steps in really understanding all of the potential that tableau has for you so again thanks for watching

Channel: Tableau Software

Views: 330,588

Rating: 4.9269662 out of 5

Keywords: data analysis, data visualization, business dashboards, business intelligence, tableau, tableau software, Tableau Jedi, advanced, design, table calculations, maps, blending, tableau server, TC on Tour 2014, Tableau Conference, Sydney (City/Town/Village)

Id: CAZ3IAJEuCI

Length: 54min 45sec (3285 seconds)

Published: Thu Dec 04 2014