TABLEAU WORKSHOP - SANKEY DIAGRAM, INTERACTIVE DASHBOARD, SETS, PARAMETERS, TWO-WAY MATRICES

and hopefully you're excited to join in as well we have a lot of content to cover today so for the purposes of this webinar we're gonna have to need we're going to need to save questions until the end so if you have any questions feel free to submit them but we'll only be able to cover them at the end that being said let's jump right in we're looking at case seven number fourteen today this is gonna be a tableau workshop it's for the company called African mobile and they want us to do a profitability analysis zooming in a little bit we're data consultancy I've been brought in to buy African mobile to look through their African business that was launched back in 2013 business has been going really well for the last four years but they want to make sure that they have an accurate representation of how profitable they are they want to look at that not only in terms of just raw profitability but also salesperson performance so they have these four requests that will run through and then there's a challenge that your own boss is provided to try and beat expectations for what they're looking at they provided us with this sales data we have the dates which are sales from the 1st of 2013 all the way through the end of 2016 we have the country we have the city the UN region the segment where the sale happened sales and profits that occurred along with this we also have salesperson data so we have again the date the salesperson number of contracts that they sold in the close rate note that this is a quarterly data set so we have it for January we have it for April for July and for October looking back at the quarter that has passed so coming back to our first question they have requested the following they're like an interactive dashboard showing profit by city tied to profit by segments and a profit trend so let's go over to tableau Connect data we'll go to excel and we'll connect to this African mobile they did they provide for us we have two sheets the sales person that sale data that we had will drag in sales data and then we'll start to work for a geographic fields you notice tableau has intelligently created a hierarchy that exists we have country we also then can drill in and we have city as well notice we do have one unknown turns out that inside of Africans Mobile's data they had just Congo listed and if you come here and search for Congo there's two options available we'll just use the first one and then that cleans up the city issue as well so now the NOLA indicator is gone we have every city on our view in order to visualize profitability we want to bring in some profit drag that on color and then just to make this a little bit more readable a little more stylized we'll do a common trick where you drop the opacity or increase the transparency by a little bit and then draw a border around that then we can up the size and the nice thing about this is you can see when options overlap you'll be able to hover over them and actually see what's going on longer this we have our profit values we want to change our default formatting a little bit so rather than just being a straight number right here we'll add in a currency I'm currently based in the UK and so I have to go through and make sure that I use the right prefixes and then we have our decimal points that will include for this purpose who actually going to just drop it to zero because the decimal point isn't as important and there we go so now you can visually and quickly see on a on a map the distribution of profits by city so have an easy one like Cairo they made a profit 142 they didn't tell us the units in this data set my guess is its thousands and then for the City of Victoria they've lost a similar amount one hundred and forty four dollars so we'll call this our profit by city looking back at our request we have our we need to now see our profit by segment as well as our profit trend so right right here this is a fairly straightforward graph profit segment will come up here to change the size to fit the entire view you'll notice that our default formatting for profit is carried over we can then make this look a little bit better by adding some color adding a label and then if we want we can mess with that to change it but for now we're just gonna leave it by profit sorry okay tight profit by segment then less thing is they want to see a profit trend generally transit down on a month level they could ask for something different so we'll need to make sure in and ask about that when they want to return the deliverable to them and now this is also looking at profit by segment then we'll include segments on color right here so we can see the profit by month by segment again a little bit cleaning come here to y-axis month of date don't really need the update so we can just change this to month then we have our profit yeah we can rename this to profit by profit Tran sorry there you have a profit by segment now we have a three views that we're looking at we have our profit by city approved by segment and our profit trend now come down here and we create a dashboard in terms of sizing I generally like to change it to automatic that way it takes up your entire view and it adjusts on-the-fly if new users access it on a different format but depending on what you need this device preview is also really nice where you can look at exactly how it will look on engineer tablet on a phone there's a bunch of different options but in this case we're just gonna stick with the automatic and so I undo a few steps there and there we go so profit segment let's add in our profit trend and now to clean up a little bit we'll want to realign our legends so we have profit matching up with a view that is being used on and we'll do the same thing with our segment legend down here dropping it next to our profit trend so now we have a really nice looking dashboard but this actually isn't everything that they've asked for they want to be able to select a city on the map and see the other chart suggest so in essence we want to use this chart as a filter for our other to do that you come up here click the drop-down choose it uses filter you can also just click this handy little button right here to use this filter so using our cities from earlier if we want to see Cairo how they're doing they're profitable across commercial public and residential although they are trending downwards in commercial and residential so that's worth looking into Victoria is having trouble all over the place but it's on a positive trend so this provides us a lot more information than just the one static graph also provides the ability to highlight things and look through it so there you go we're about five minutes in and we already have a nice interactive graph that shows not just our profit by city but also by segment and that trend over time and they all interact with each other this is one of the main benefits of tableau and something that really Wow's people when they see it for the first time I remember when I was first exposed to tableau seeing this type of dashboarding capability compared to the raw PDF reports that we had that just got auto-generated and emailed and senses attachments was like opening up a whole new world to me as it was incredibly exciting to see this kind of capability next African mobile has asked us for an interactive scatterplot showing the relationship between profit and sales by either a region country or city depending on their choice so we want to create a scatterplot we also want to use a parameter to make that choice possible but coming in here few bits more cleaning let's come in with Heidi sheets and then we'll call this our profit dashboard and these sheets are still available to us if you zoom in there that you can go to the sheet and you have access to it it's just a nice way of cleaning up your workbook to have it hidden when you know that you just want people to look at your dashboard so going back to our question we want to look at profit we want to look at sales we all change this to a circle up the size a little bit just so we can see things easier now we have a profit we have our sales we can go in sales let's just do a little bit of cleaning before we jump in and currency change this $2 and then get rid of our decimal places and now we have our sales mapped against our profit this two-way scatterplot is a great way to show the relationship between values especially when you start adding more detail so our region into our hierarchy and we'll add that on color and now we can see how to see the relationship between sales and profit for all five of our regions again I said is a very useful chart because immediately now we look at Western and we know that they are one of them they are the leader in sales which on the surface who seem really good but they're also the least profitable business out of any of our regions so that's a great question to ask you can also come down to southern and northern and say ok if you are selling so few in terms of sales how are you making that much profit so you can look learning from best practices here and hopefully apply them here in Western Region to try and clean things up a little bit but aside from the analysis going back to the request of African mobile they want to be able to see the relationship between profit and sales which we have but they wanted to see it by either region country or city depending on their choice now with this hierarchy we could drill in and you start now you see the country level of detail you can drill in again and see City but with the colors aren't matching what we need right so you could do this multiple clicks adding colors then it starts getting really messy really fast really hard to read plus then we have to rely on the end-user to do all of this and understand and know how to use tableau to that level so what we're going to do instead is we're going to create a parameter that allows the users to choose what they want to see so coming down here we'll right-click we'll create a parameter this parameter is going to be called choose a dimension as a best practice whenever you want people to interact with your dashboard it's best to create create label names or parameter names that prompt that action so choose a dimension we want them to choose between a region country and city so we have these three values click OK show our parameter control we have our new dimension that we're working with we have a new parameter sorry and let me just get this bar out of the way Viveca I'm gonna change it to a single value list so now you can select between region country and city we drag that up to color that doesn't actually give us what we need because if you choose country city you notice that it's only returning one value in one color because that's all the parameters telling us to do so what we're gonna need to do is create a calculated field on top of this that will then look to the parameter and pass through the appropriate field or dimension that we're looking for so create a calculated field just use alt AC for a shortcut for creating that we're call this chosen dimension and we're gonna use a case team so case choose the dimension when region then we wanted to bring back the region field when country we wanted to bring back country and one city we wanted to bring back city for those of you who haven't used case statements before you can think of it like an if statement so if choose the dimension is equal to region then we want region if it's equal to country then we want country if it's equal to city there in one city rather than having to type that equals each time and make that comparison saved a few typing steps with using the case statement no we now have our chosen to mention and if we drag that to our color we now have our region graph that we recognized before we can then click on country and you'll notice that it's updated not only with the new level of detail but also our color scale has updated to show our new values and then we have city as well nebith in this we have a fantastic example of what's called an outlier so we have our profit we have our sales generally they're pretty tied together we have as your I mean there's no strong relationship going on here but there is some correlation but then all of a sudden we have cartoon up here they only have 17,000 in sales but they're among the leading cities in terms of individual profit two and 34 so this creates a great opportunity that we can see very quickly to go in and say what are they doing what are they doing well and how do we replicate that in these other cities so here we'll call this profit Versailles and then again we can come in change the opacity to 80% and add a little bit of a border helping us see the circles that are overlaid on top of each other okay looking at their third question they're asking for an interactive salesperson alysus so interactive got another clue they want either dashboards or they want some parameter they can select showing contracts sold by each person as well as a second chart showing the difference from a selected salesperson so the hint was to go through and use bar charts as well as a parameter and just kind of conceptually what we're looking for I'll explain as we go through so first though right now we're connected to just our sales data we need to go back to our data tab create a new data connection to our same excel file except this time we'll bring in our salesperson data again this has the date in terms of the quarter we have each salesperson we have the number of contracts they sold and their close rate but in this case we're just trying to look at a comparison of contracts sold after this is the entire view and then we'll sort that that's what we have is we have each individual salesperson we have how many contracts they've sold throughout the entire data set and then we want to be able to compare these people between them so if we're looking at a divorce if we select him we want to see that Priscilla Terry had five more sales than him and that Ken railings had seventy less sales than him that's the type of view were going for but in order to do that again a selected salesperson says use a parameter we need to create the functionality so that our user can select the salesperson so we can come down here and create a parameter but then we'd have to type in every value by ourself if we come up here to salesperson we can click create parameter and what tableau does is that intelligently chooses the data type and then provides a list of values based off of the values that are available under your dimension so we'll call this choose salesperson and then we have a list of salespeople to choose from we can show that parameter control and now we can select the values that we need one quick note and a lot of online questions and forums you'll see people asking for dynamic parameters that can mean a lot of things but one of the things that it can mean is they want this to be able to update automatically as it stands now though if you do this and create a parameter based off of it you will have to manually add a new salesperson if you add new salespeople to your data just kind of a good best practice to know because a lot of times you can have this parameter and then nothing new shows up and you're scratching your head trying to figure out where your data went anyway that was a bit of an aside today we can go through and we can select a sales person but we want to create a few calculated fields the first one is just to find out our selected sales so we're going to do if choose a sales person is equal to our sales person then we just want to look at the contract sold I think you accomplished they're bring out the selected sales into our view and we have a challenge what's happening is that for Francis we get 151 that's exactly what we want but if we look at Aden if you look at Audrey if we look at anybody else other than Francis it's returning a null what's happening here turns out that this calculation that we're doing is one that is affected by the context of the view so in other words we're looking looking at our data we can go through and we say okay for Alan Morris sum up the contracts sold is 13 great but for selected sales it's doing an if statement first its saying if the salesperson is equal to our selection in this case Francis Martineau then sum it up if not return null so it's actually returning no values so how do we get around this starting at a below nine and then expanding on its capabilities in tableau 10 there's a feature called a level of detail calculation these level of detail calculations that make it possible to calculate data based off of data that's not in your view and based off of level of detail that's not in your view or you can exclude level of details in your view and then make it possible to sum up different values so in other words in this case we want to be able to get this 151 without taking you a count salesperson so I'm gonna call this a little detail selected sales and the syntax first level of detail it starts with a curly bracket and then you choose either fixed include or in this case exclude then you choose which dimension you want to exclude so in this case we're going to say salesperson you put a colon and then you tell it what you want to return for this visualization we want contracts sold and then you close off with a curly bracket and let's visualize that and see what it looks like first stuff we have to go in and we have to fix a label and now we have and if your this is great this is something that will happen a lot because when you're trying to return selected sales you want to make sure that it's tied to your parameter not just your values in general this is actually a good example of how level of detail calculations can return something funny that you're not expecting so if you look at what this is saying is saying okay exclude sales person so don't take into account the fact that Audrey's here and I want you to sum up all of your contracts that have ever been sold in that case it returns to some of the contracts across the entire dataset so it all told we've sold almost 1500 contracts as a company we actually want to do is return not every contract sold but our selected sales that we calculated earlier so now if you look at Francis Martineau she had 151 contracts sold and we have 150 one being record returned for each individual value this gives us exactly what we need to be able to go in and create a new calculation so we'll call this difference from selected and we will be able to say we want our sum of contracts sold minus the sum of our level of detail calculation so saying we want this 203 minus 151 drag it up here and we get the view that we're looking for again we need to go in and change our label because right now looks kind of funny and you notice this gives us exactly what we need so we have Francis Martin 151 difference of 52 - Audrey Baker let's switch these right here and clean up a little bit we'll add color right there I first ran across this on one of the tutorials that tableau has has posted and I love the color palette that they used they actually added one more feature to this and it's a way to mark which sales person you've selected because right now as it stands it's easy to see kind of the crossover between positive and negative but if you have a lot of people who are tied it can really mess with your visualization so they came down to the created a boom e'en where you got a salesperson is equal to choose a salesperson basically they're saying if our salesperson is equal to our parameter choice that we have then we need to be able to show that and see it in our view so if we drag that to color we now have this built in but we don't want the blue and the orange competing with what we already have so if we come here to false we change that to white to true we'll change that to gray and then we'll come back and just so we can see it we'll add a border and while I you have a very beautiful looking view that shows a difference from our selected salesperson in terms of the contracts sold and we can clean this up a little bit by hiding that card editing the title showing difference from other scale and we have the interactive self-driven reporting that they were looking for we'll call this our salesperson comparison okay forth they're asking for a two-way matrix overtime tracking sales person quarterly performance against their company benchmarks for contracts sold and closed rate we have these benchmarks that we'll add in and then we'll use the pages functionality to show the changes over time so coming in we want to look at close rate we want to look at contracts sold and now interestingly enough when we look at this data it actually does it make a lot of sense right so a close rate is a percentage if you look back at our data that we have Kosar a 0.5 5.3 so 55 percent 30 percent 36% and yet we're getting values above 50 what's happening here is rather than taking the average of a closed rate or taking some other aggregation tableau is actually going in and summing up all those individual percentages so we change that if we want there's a bunch of different options that we can use but we'll just use average right now we'll go in we will format that get it back to a percentage we don't need the extra that's right there and then some of contracts sold will also drop that to an average just so we can see kind of your average salesperson percentage in order to get the detail that we want for review we'll add sales person to color and we'll change this again to kind of our standard circle up the size a bit so we can see how salespeople react how the salespeople compare to each other and we've got a great view that shows what they've asked for two-way matrix tracking salesperson quarterly performance against benchmarks we'll get back to the overtime bit in a second first we're going to add in these benchmarks so for contracts sold they want each person be compared to ten contracts and then for a close rate there were they want thirty percent so this is a good example of where we can use reference lines reference lines can be driven by the detailing of you but can also just be a constant value that you pass in which becomes especially helpful for benchmarks so become a peer drag a reference line to close rate we have several options they be driven by your Cloverfield itself or if you just have a constant in this case we want it to be 30% notice the formatting carries over and then we'll add another reference line to our contracts sold and we'll call that constant and we'll be going in on ten now visually this looks a little bit off in terms of this this x-axis being a bit further than we want also we need to format this to get rid of the extra zeros down so we have ten and then I'm going to come in and just fix the axis really quick fixing the axis is a handy tool especially when you know what your data looks like already so you're able to create the view that you want that being said it is important to recognize that when you fix the axis it will not change even when your data updates so it introduces a lot of challenges that it introduces the potential for things messing up as you look forward to the future but now they want to see a two-way matrix over time how do we show these values over time we know what the averages are and we know how what they look like at this overall perspective but the tip says use the pages functionality if we use our date up here on pages once a year but we actually show quarter so quarter date now what we have is a page for each individual quarter as we click through we can see the dots moving across the screen to their different values showing basically how that relationship changes over time and how the salesperson relative performance changes over time pages are great too will be used they're very handy in terms of creating a visually interact with you you also press play and it animates it for you so you have your animated matrix that's moving around you can increase the speed on it if you want you can decrease the speed generally I stick with just the suggested two but sometimes three is fun just to watch things bounce around now well this is useful at this ending point what would be more useful is if we could see where they've been so Priscilla Taylor one of our top producers it at the end of 2016 has she always been this high or for instance ade Morris has he been here as well so what we can do is we can actually come up here show the history and then we have a few options that can we can use we do it for selected manual or all in this case going to you all and you can either show the history for marks I personally think that that ends up looking a little bit cluttered even though it can look nice in some cases I like using the trails and then rather for the entire length which shows every single mark in your view I like using the last five so looking at those now we have a fantastic view that's showing us where each salesperson is currently but also where they've been before so we mentioned Priscilla Taylor before she has started out in kind of his leaders quadrant where she was doing really well on contracts sold that she was also doing a lot on her clothes rate which he's only gotten better since then for Mary Ann James she is closing like a champion so when she goes an appointment she is very likely to sell but she's not selling that many contracts I need to push more leads her direction this also lets us see people like Michael Smith do like Laura Jaeger who really are underperforming and we may need to have a discussion about what we do with them going forward but this chart actually has a special in my heart at least it's in terms of data visualization the company that I used to work for was trialing tableau they wanted to use it they wanted to see what it was useful because we had a very well-respected DBA who had worked with it before and was recommending it he was brilliant so like yes we need to make sure that we look into this product we got in the free trial we connected to a few data sets and it was useful but not really showing much compared to what we had through other solutions but then we built a Maitre D' similar to this it had close rate it had contracts old it was on a monthly basis but the principle was still the same and at first we were just really excited because we built a cool chart but then on the sudden or cubicle started getting very cramped we had members of not only our team but ended up having the entire executive team inside of our cubile cubicle sitting there watching these dots change over time watching this performance tracking and it ended up being an amazing experience where everybody saw data in a way they had never seen it before there's something that the visualizations from tableau give you that we ended up buying tableau right away and we use it ever since and it more than paid for itself through creating visualizations like this and other opportunities like I mention with the executive team to show them our data that we had seen before but in new ways that really led to quick insights so we'll call this our salesperson clothes rate and contracts so great so at this point we've actually gone through everything that African mobile has asked but then you have a challenge turns out a boss is recognized that African mobile has long experience with tableau and they're not easily impressed so she's asked you to cap your workbook off with a sinky chart showing how sales flow between segmented region to really blow them away for those of you unfamiliar with the Sankey chart come here and we can show a quick example a sink each chart shows the flow of one of your measures or some value but Queen to different segments so here we have it falling from electricity and gas - sorry rather than two different segments two different dimensions so we have it based off of the dimension of what type of energy you using and then what that energy is used for so wave electricity used for heating lighting and food storage we have gas used for heating used for water heating laundry and for cooking and so it's a really visually stunning graph that maps it with a beautiful s-curve going across and really makes your data pop granted there is some debate about how readable the this chart is I'm not gonna address that right now I'm just gonna show you how to go in and how to create this in tableau and leave the use case is up to you and based off the needs of you your company and your clients it also is worth mentioning that this sinky chart in tableau is not one of the built in charts which is why your boss wants it because it's a really kind of powerful thread but it's worth mentioning and credit is due to people like Jeffry Schaefer Olivia Katherine Noah Salva Terra Joe Maiko and Chris love whose technique we're going to be demoing who have contributed a great amount of time online posting tutorials and really putting into mathematical development to create something like this and push the limits with what tableau has anyway so enough of an introduction on that when we're looking at a sinky chart we have our data like I said flowing from one dimension to another so going back to the request we want to show sales flowing between segments and region in this case we're going to have segments on this site we're gonna have region on this side now how tableau interprets data in order for the sinky chart to work you're actually going to need two copies one copy of your data for your starting point on segment and another copy of your data for your starting point for your ending point on region now if we wanted we come down here and we can select our entire data set copy it and then append it to the bottom so that we now have two copies of everything but that runs into issues especially if the data changes or if it updates at some point then we have to go back in and redo that manual work with Excel and so we wanted to actually demo a way to create that duplication of the data that we want as well as some additional feeds that feels that we need using tableaus built-in capabilities so coming in we're going to need to go to our data tab connect to a new data source connect to an excel file and we want our African mobile data however instead of clicking open like we normally do we're going to come down here and open with a legacy connection so in the past tableau connected to excel file using a using Microsoft in jet engine and what this did was it gave them it introduced different possibilities that turned out they didn't need anymore so they discontinued it in future versions but they left that legacy options still available for us and in this case we can leverage it for what we need so just help keep things different well name is data source our Sankey data set and then we have our sales data you know this is just like the other data that we brought in we have a thousand 256 rows starting from 2013 down to the end 2016 and then we're going to leverage what I said about that custom connection when you come up here and then definitive data tab there's a few options that aren't available normally when you use tableau the new excel connector one of those is a convert to custom sequel option what this does is it shows the query that tableau is using to connect to your excel file written as if it were in sequel length sequel a structured query language so for anybody who's not familiar with sequel the basic syntax is you select a bunch of columns from a data source this is just selecting everything from our excel file you'll recognize our column headers here bringing in from the sales data tab now we're going to copy this pair it with Union all which says append everything from this second group of text and we'll just paste this connection from before effectively duplicating what we already had so if we hit OK on that you can see our numbers have now have gone from 1056 to 2112 duplicating our data like we needed to have happen now going back to our Sankey chart we're going to need to know which data set exists with which one so we need to know which side of the Sankey chart we want our data to be on in this case we're again doing segments connecting to region so I'm going to go back in to our custom sequel query I'm going to add a column so first we would say segment as this side and then down here we're gonna say region as Vince site now this biz side is just a filler I could name this anything I want I can name it baseball if I wanted it really doesn't matter but this side is kind of a contextual clue for how we're going to be using this column so it's always a best practice to not name it something like baseball but actually name it something that will help your users and help you in your data prep process also important to note for those who don't know sequel you need a comma between your columns so we have a comma here a comma here and then lastly when you're unioning you need all the column names to be the same so this is vis side this is vis side and we click OK notice it adds a new column so we have this side for segment and then we have this side for region again going back to our view we now have the left side of our data set and we have the right side of a data set and we're able to use those to develop the Sankey chart even further so now that we've duplicated the data we've basically taken care of our vertical data sources that we'll need will be able to calculate a percent of total for the segment a percent total for the region but what we're going to need to do now is create these marks that go across the view horizontally there are multiple ways to do this but the best way to conceptualize it is to think about when you drag like month of date up to your view we're back to our profit trend if you look at this you see we have month up front and then the sum of profit what's a blow is doing is it's going through and for each individual month across this horizontal axis it's looking to our data set and pulling in the some profit so for commercial month of August 2013 the profit was 175 looking back though at this sheet we have our vertical data set but we don't have anything horizontally because there's actually no scale that's going on between these two so what we're going to do is we're going to need to create the data that go in between these two charts creating the flow back and forth having done this before you need to write about 50 marks to make it flow nice and smoothly and create the nice s that we're looking for if you have fewer it ends up looking jagged so we need to do is create the values 1 through 49 in our view and there are multiple ways that we can do this so I not interview but in our data set and then in our view one way to do it would be to go into our excel file join it to itself and duplicate every single row 49 times label them 1 to 49 and then that would give us the marks that we need to draw these lines that massively increases the size of your data set going from whatever number of rows you had multiply that by 49 and you can run into some major performance issues so we're gonna use a more advanced technique that's called data densification this is something that tableau recognized as not a master technique but they'll call it a Jedi level technique it's something that is very powerful and it helps tableau write data that isn't there so where to come in and we're going to create a field called to pad this is basically creating the endpoints between our two ends of the line that we want so looking to this side whoops I don't need that yeah I need to be using our sinky data set sorry to pad if this side is equal segments so starting with the left side of our view then we want it to be one else 49 so we're doing right here is creating our end of endpoints we're saying on this side it's going to be one on this side it's gonna be 49 if we come here we drag to pad up two columns to screen and then you can see our number of records what we've done is created as expected a thousand and fifty six is the number of records that are associated with one so one copy of data set and a thousand fifty six values for the second copy of the data set which is 49 but what we need to do in order to build this s-curve we need to have access to those discrete values between these two numbers so between the 1 and between the 49 so we're going to come down to our 2 pad right here and we're gonna create bins so bins are familiar for anybody who has used a histogram before but the bins function has a special capability in tableau in that it is range aware so if we look at our view now we have our values at 1 and our values at 49 like before except this time now we have all of the values in between when I say it's rain to where that means that tableaus bin function looked to two pad and said ok we have a minimum value of 1 a minimum value of 49 and so we want to create data for all the other values in between even though it doesn't exist in your data set so in effect we've created those other 48 copies that we need without actually copying any data this does put a little bit of strain on tableaus data engine but when you're working with smaller data sets it's totally fine and it's a really incredible advanced technique that will show more up as we go throughout then working with this we also need to create one more calculated field I'm going to call it t for reasons that we'll see you later and we'll just use index which returns the current row of the partition so it's just a way to track your data as it goes across call that index over 25 index minus 25 sorry and then we'll divide that by 4 this is a technique that's known as jittering it takes the relative position of your mark moves it a little bit and then divides it by another value so if so it moves the position of your mark just a little bit what in effect this does for our sinky chart is it evenly spaces all of our marks across the view from left to right so now we have T we're drag padded over here to detail give it a number of records and we'll check T up two columns now you notice that it's a table calculation index is a table calculation so if we compute using table across when they have our two values but then if we compute using padded kept using padded which is that data that we densified and then change the circle we'll find that whereas before we only had our two values we now have a value for one two three four five all the way up to 49 looking back to our example it's always helpful to reference back to the picture that we're looking for in the end we now have all of our marks across this view that we need again incredibly awesome tool data densification is a really impressive and very technical skill that if you have and will really impress people who are looking to understand how well you understand tableau so this is very exciting and now that we have the horizontal worked on we need to drop back to our vertical because the vertical data set also needs to understand how electricity and gas sort themselves so we'll have these subcategories and you have a value from 1 to n depending on how many categories you have and for the Sankey chart to work you need to have that value on your left side with your segment and on your right side with region so we're going from 1 to N and then in order for them to be a nice flow to create this curve rather than just straight lines across we'll go from 1 to N on segment and from 1 to n go in the opposite direction on region so do that will create a rank functions and for these the request has been to just look at our sales over time and have a flow between the two dimensions so I'm gonna show our some sales and if I buy our total or sum of sales for those familiar with table calculations this is just a sum of your sales across your data set across your view sorry and then divided by the total which is the sum of sales in the entire data set and so you end up with a percent of total that will work for you so that's prank 1 and then we're gonna duplicate that and create the same thing and we'll just call it rank 2 it's the exact same field all I've done is duplicate it and rename it so now we have T we have Rank 1 rear rank - we have padded so we have the functionality to inform where the locations are on either side along with that though we also need to program in this curve that's happening so this curve is actually something called a sigmoid function from Wikipedia it's just a mathematical function having an s-shaped curve you can see it right here in this example often refers to the special case of logistic function shown in this figure again and defined by this formula this is the exact formula that you're going to use and if you're skipping ahead of me you've noticed that it has a T in there so that's why we called that other column and that of a calculated field that we created T for use in this calculation so we're gonna call this the sigmoid and we want to make it 1 over 1 plus with Mac at our reference e to the negative T tableau doesn't have a built in a function but it does have exponent which exponent it returns earase to the power of a given number so we say exponent 1 raised to the negative T then we have our sigmoid calculation this again is just giving us the curve in our line so we're actually going to need one more calculation that actually returns the actual curve so the curve is dependent on where you're starting and where you're going so just in this quick example if this is 1 2 3 and this is 1 2 3 4 5 6 7 8 if we want to go from 1 to 8 the curve function needs to understand that and we'll use our two ranking formulas to tell that so our curve is going to be equal to our first rank plus the difference between our two ranks times this sigmoid so basically times that curving function this rank 2 minus rank 1 affection if effectively sorry swaps the direction of your second rank and so you're running from top to bottom on the left side for segment and bottom to top on the right for region so that was a lot if you're still following give yourself a high-five if you're not quite following keep watching we're about to get something more visual rather than just technical and dealing with a lot of calculations so we can now start by adding our two dimensions that we care about to the view we have segments and we have region and then we're going to add our curve to our rows all right so if you look bad here our chart our curve this s-shaped part of the Sankey chart needs to go across these rows that we've created now if you add it at first you'll see we're really having issues there's 705 nulls you only have two marks showing up on the screen and that's because to make curve work you actually need to come down an edit table calculation and use something called nested table calculations nested Cape table calculations give you the ability to work with different fields and treat them differently depending on how the table calculation accesses them so for instance we can choose rank one and say here's how this table calculation curve will behave when it's looking at rank one here's how it'll behave when it's looking at Rank 2 here's how they behave when you're looking at T so let's start with rank 1 with rank 1 we don't want table across because we don't actually have all the values in our view that we need we needed to comprehend padded but we also want to specify the direction so we want to go from segment to region and also include padded then you click and you drag this up to the top to create that order of that we were just talking about so now we have our segments we have a region a padded and you'll see this is a little preview showing up to where we're starting to see mark show up on our screen it's very exciting bring this over to rank 2 we're going to want to do very similarly except this time we want reason to show up and then segment after that now as we order the way in which these communicate with each other all sudden we're starting to see some very exciting things happening in our view and the teams make sure this computer to cross padded so we have the entire left-to-right version of our marks available to use so looking at this all sudden we're starting to see a singie chart we have our values from 1 to 49 they go across the view and then when they need to flow differently they are using that sigmoid function to create the curve now at this point I'm gonna take a little bit break I'm just gonna clean some stuff up I'll allow people the chance to kind of think about what we've been working on and hopefully this all is making sense it is not a easy topic so well done for attempting something this big we're gonna clean this up by going in fixing the axis at negative five that and five that gets rid of this kind of jump right here that has to do with the day-to-day densification and how tableau handles it so if we do negative 5 to 5 we're not losing any additional value because we don't need those extra marks we just needed the marks to build the curb across then you come down and you can hide your header for the curve we also want to go through and make it take up the entire view so because we know it's a percent of total we know it can just go from zero to one and then we can also come in and we can hide the header but one thing I want to work on really quick is that if you look right here at our top marks we have our residential segments in the center we have our public segments showing up and then in the bottom we have our commercial segments going back to our profit dashboard that's actually the opposite of how it normally shows up alphabetically commercial public residential that just has to do with how tableau handles the ranking function so we can fix that really easily by coming in reversing the axis and now we have commercial public and residential just as a way to keep consistency across our workbook so I change the get rid of the curve header right there and now we have great-looking chart that is circles for now but we'll go in and we'll change it to line and then everything breaks this is kind of common as you're practicing with table calculations as you're working through nested table calculations is a different dimension how these different levels of detail things like this maybe not this exact for you but your view breaking will be a very common symptom so don't feel bad if you ever get there I do it all the time what's happened is we're now drawing marks as best we can but tableau still can't comprehend how it's supposed to draw between these marks right so it understands that there's a value of one here somewhere it was a value 49 as well but going in between the marks it doesn't really know what to do shows that if you drag padded over to path then it tells tableau draw the line over that data identified data you created for padded and now you have this amazing looking Sankey chart that's showing up very exciting we're almost done so hang in there if you're falling behind a little bit or if you feel like you have questions what we now need to do is if you look back at our example again from this width it's actually sized by the percent of total or size by the individual value inside of each line now we want to do that but if we drag sales up and try and put that with size you know so we have a problem if you look in the tooltip you see the sales are blank this is a similar situation to our salesperson comparison when we first built the difference from selected without the level of detail what was happening was it tried to sum up the values for Francis Martineau when your salesperson was equal to Constantine eager and so since there was no value there it didn't return anything other than a null in this case the value of 13 doesn't exactly exist in our data set anywhere and so when tableau goes to try in some sales it doesn't work and it returns nothing because I didn't find anything but we know that these values exist in terms of the densified data so what we can do is we can trick tableau into providing the data that we need so we'll call this a sizing of sales sizing for sales well do is do a running average of the sum of sales using again a table calculation Jagga up to size have the same issue as before because we're calculating for table across with the values that exist but if we bring it down and tie it to our padded data set now we increase that you'll see that there's a relative size difference between things it's able to comprehend where we're looking at across our view and then size our marks accordingly so we'll come in we'll clean this up a little bit going with format get rid of our gridlines we'll get rid of our 0 lines and then to really top things off we'll add in our color for segments but also for our region and now we have this great view for our say a chart where our sales are flowing from our segment on this site to our region on this site so high five everybody for keeping up this long this is not a beginner's chart and it's fantastic you've been able to join us for this but leaving it just like this while interesting doesn't provide as much helpful information as could be possible the way to really make this pop is to combine into a dashboard so we're gonna rename this as a sink II sales chart sink you sales flow from segment to region and then we want to do looking back to other example is we want to create these percent of totals and then wrap your chart on either side with them so this should be more straightforward than other things we will have our segment right here and we'll add in our sales that's how I'll drag segment color there you'll notice that that it's using its automatic color pattern we will need to change that to green just to match with the styling that we have on the other sheet now we can add our self sales but we do want to make sure that this is a percent of total to the top and then we'll also add segments to our label coming out a percent of total we'll come in and clean this up a little bit want to be a percentage but we really only need one percentage point and then might be helpful to list the segment first so we have a percent of total by segment that we can then use as a reference in our dashboard so we'll call this segment reference then we'll just duplicate that sheet and set a segment we'll call it region at a region to detail are moving the colors for for reasons that will be seen in a second and we have our sum of sales again being computed using our table down to get our percent of total so now we have our second reference we have our region reference and we can create our dashboard so I add in our Sankey chart first we'll get rid of these values these legends sorry and then we'll add in our second reference and we'll add in our region reference close out it again because the colors are labeled so we don't need the extra info and then to make things line up we can get rid of our headers and it will also hide the title and then we can play around a little bit again we'll make this automatic filler view a little bit nicer and then now we have our flows from commercial over to the different regions and we have our flows from sorry we have two flows from commercial from public from residential from all of our segments over to our different regions and then you see what the reason that I chose to not do anything with colors on our region reference was because if you had blue flowing to both blue and green and orange and red it would confuse things a little bit so coming back here looking at our color palette we can actually drag region to color but instead of our colors that would clash with what we're using let's just apply the Grey's color palette and now in our dashboard we can see the flows going back and forth now there's one more thing that we can do to make this really interactive and really highlight the flows that are happening we can go in and we can start using some dashboard actions to highlight what's going on so we'll add an action not a filter by the highlighter call this our segment highlighter and we'll have to use the segment reference as our base chart and we want to apply to our Sankey sales but then leave our regions unchecked because we want to still see all the different regions we just only want to highlight the segments that we're interested in adding a similar thing going the other direction we want our region highlighter will go from region uncheck segment and then have it on hover now with these actions we've had a whole new way to interact with our dashboard if you hover over commercial you can then see your sales flows as they go from commercial to public to residential you're gonna see the movie the other direction across your different regions now I'm going to go in real quick because we have the highlighting doing the action that we want I'm going to remove the tooltips because those are just getting in the way and then right here last step remove that tooltip come back to our dashboard and now we have this great-looking Sankey chart that hopefully will blow away our customers and will really show them we know we're dealing with tableau and that's there are very exciting possibilities in the future working with us so that is our case studies today as our workshop on working through tableau we've covered everything from interactive dashboards to sheet swapping or measure swapping or dimension swapping which is what this is called then you have your salesperson comparison with level of detail calculations you also have interactivity built in with with parameters here we have our pages functionality to show animations over time and then at the end we capped it off with a Sankey chart so thanks for attending everyone it was great to have you joining us hopefully you can use these techniques in the future recordings of this webinar will be available on super data science comm and hope to see more of you guys in the future again just looking through turns out there have been a few questions my colleague isn't working with you guys to answer those real quick and there's only a few left so it looks like we're doing well feel free to submit your questions I did have to go through things quickly just because of the amount of content that we're working with so if you want questions feel free to submit them if you're go to you're also welcome to email us get in touch with us afterwards and we will be able to answer your questions on a one-on-one basis as well in okay so we had a question come through what's the best way to close the gap between the references and the start of the sinking this is one of the limitations of this approach and just abusing a Sankey chart in general you're gonna have to play with manual sizing so have to come over here and rather than having it fit the entire view you're just gonna have to have a standard and then you can you go in to your segment sheet and you'll size this appropriate for what you need you can make it you can make it wider right there and then coming back to our chart and then we'll connect all the way up to the edge of your view and then you can close that gap right there I didn't do that because for one interest of time but also because if you're using different devices then that gap sometimes can show up even though you've eliminated on your device especially when you're working on a laptop like I am connecting to desktop users the way to completely remove that is to actually change this from a writer here rather than a tile live container making it a floating layout container and then you expand that out to fit your view and again you can see it's a very manual process it is one of the limitations of how the sink each art is built in tableau so but if you do it with floating then you'll be able to clean up that gap we're moving entirely so it looks like it's flowing straight out of segment over to region okay along that some of you have asked if these files will be available on super data science afterwards and of course this tableau workbook from the webinar is going to be saved and packages online so you can have it and access to it you also will continue to have access to the data as well as the questions so you can go through and practice it yourself those data files the tableau workbook they'll also be posted with a recording of the webinar so you'll be able to work through along with me to mimic what I do to follow it and hopefully be able to nail down that technique and ideally we can find ways to do it better than the ways that I did it and you can send us feedback and I can learn from you guys as well just a quick thank you who attended as well and their activity and the questions that you've asked it really helps us to create a better product for you but also to show visualizations to show ideas and to show ways working with tableau that you guys are interested in and we'll really be able to respond to what you guys want any what your demand is so it's very helpful for us thank you so we have another question coming in about any restrictions that exist and the wording is a little bit vague so seems like the question is asking about are there any restrictions on how you can use Sankey charts especially when you're worried about kind of the technical performance tab well I think I'm interpreting that right okay so if you look at your restrictions on Sankey I mentioned a couple times that you can run into problems very quickly with performance especially if you're duplicating a lot of your data or if you're dealing with a very large data set so this technique where you apply the data densification is a fantastic and advanced technique but it can run into issues when you try and ask tableaus data engine to do too much there's another method piloted by olivier katharyne the who's name i mentioned earlier who uses rather than data densification to create the marks actually duplicates their data a bunch of times to draw individual lines but then only takes the top line and the bottom line and then fills it in in between with a polygon to make a much more performant Sankey chart that's able to match big data sources a lot better so I'd recommend recommend looking up his stuff Olivia Katherine Sankey just Google that really quick and you'll be able to find his technique we're also hopefully going to be able to do a tutorial on that looking going forward where you can show the way to create the Sankey chart that one tends to work better for really large datasets this one is better suited for specific datasets and relatively smaller the the restriction will depend entirely on your computer on your processing power that's available a kind of ballpark rule is 5,000 rows but it's 100 percent up to your computer and how you work with things and okay that covers all the questions that we have again thank you everyone for attending it's been awesome we're super excited about the demand that has happened and the number of people we've had attend it's been awesome working with you and walking through this case study together and hopefully we'll see more of each other in the future thank you

Channel: Art of Visualization

Views: 58,865

Rating: 4.9507041 out of 5

Keywords: Tableau, Tableau 10, tutorial, sankey, diagram, tableau public, tableaux, tableau tutorial, tableau training, tableau online, tableau excel, sankey diagram tableau, tableau sankey, sankey tableau, sankey diagram, sankey in tableau, sankey graph tableau, tableau custom charts, flow chart tableau, data analytics, sql, data, sankey chart, superdatascience, tableau sankey chart, interactive dashboard, dashboard, matrices, parameters, sets, tableau skills, tableau workshop

Id: u11t3R8R1O4

Length: 62min 57sec (3777 seconds)

Published: Wed Sep 27 2017