Visualizing Survey Data using Tableau

okay I've been given approval to officially start the session this is a visualizing Survey data with tableau my name is Steve Wexler on the founder and principle of data revelations and I've been using tableau since the Australopithecus era I go back to 2006 have been using it since version 2.0 and in fact have been visualizing survey data with tableau since then and it probably accounts for about 40 percent of my practice so very briefly I'm going to show you our agenda which is we want to take stuff that looks like this and turn it into stuff that looks like this and like this I like this a little bit better than that please much much better thank you so first part I don't know if you're creating the survey yourself or someone is giving you the survey and saying here's all this great data please help us glean and find amazing insights in it but know what the survey is so I will ask people will send me data but I will always ask could I please see the survey instrument either a printed copy of the Word document or whatever they use or I'd like to see it online and see how is it presented so I like seeing this so this is a typical Yes No maybe question here is a check all that apply question okay so I'll see where this is in the survey this is a Likert scale question how many people went no that's not how you say that word it's like eart the guys name was rensis likert Li ke RT sort of like ELISA think e Li SSA and everyone thinks her name should be pronounced ELISA but it's ELISA she's the chief marketing officer at tableau the guy's name was rensis likert the correct pronunciation is liquor in any case this is where you're welcome whomever is the either you know they're so do you lose bar bets over this when people say no it's like or it really is liquor you to be the the I always have to explain that because people think oh this guy is supposed to know what he's talking about but he doesn't even know how to pronounce the scale name the guy was the founder of the human resources Institute in any case this is a attempt to apply quantitative measures to qualitative notions so to what degree do you agree agree or disagree with the following statements and not at all small degree and you would apply a scale like a one two three four or five to something so there are a couple of other type of questions that we'll be looking at like a benchmarking question which is on average how many hours a week do you spend on such-and-such or what is your annual salary but this should cover quite a degree of them and you should see okay how does that stuff map to the data I've been given so the Yes No maybe question might look like this in the spread I'm going to be dealing with the data in a CSV file or a spreadsheet but it doesn't matter you can live in a big honkin database the concepts involved are exactly the same so here was our check all that apply question and you've got the yeses in the nose and here's the Likert scale indicate to the degree to which you agree or disagree with the following statement now often when you get your survey and you're downloading it from whatever survey system you're using you can either download the results as text responses or as numeric responses so when somebody asks you well which do you prefer say yes you want them both ways so here is it the do you measure these things yes or no and here's how it might be rendered as zeros or ones I hate survey systems where they put a yes and then a blank in and the problem any of you know why there would be a problem if you just had a yes or and a blank because you can't tell if they ditch the question completely so you have to look was everything blank then they did answer if one thing was selected then you know they answered something and you know to count the question so I like to see zeros and ones not ones and blanks or yeses nose same with the check all that apply with a Likert scale question don't we had it there oh this is what do you measure oh I guess I left it out where you'd have the a one two three four or five that goes with the not at all a little bit neutral agree strongly agree all right so hate when you have a slide to when the some guys trying to talk that has a lot of verbiage on it bottom line is biggest impediment to success with visualizing survey data is getting the data in the format that you need to be in that's kind of true of everything with tableau so I'm going to spend some time and discuss new ways I've discovered to take the data that you may have getting getting in a format so you can really be fast with analyzing stuff how many of you have either read some of the blog posts I might have had on reshaping data or the tableau survey let's have low white paper on how to do this you're welcome I thank tableau for having me right that the I'm going to show you a few things that go beyond things that I've kind of learned in the last six months twelve months that will help a little bit more so let's do a demo of this I'm going to use the pre reshaping tool that comes from tableau and add-in for Excel and I'm also going to show you how to do it with a I think is a considerably more powerful mechanism which is a the ETL the extract transform load facility that's in all tricks and I do encourage you to check out a demo of what they've got going on so let's take a look at our data see what we've got and what's going on in it so from my survey provider whether it's Survey Monkey or one of the really high-end sophisticated tools like Qualtrics I've gotten this excel file and here's the data as rendered as text responses here's the same data as numeric responses I hope that looking at this it resembles your data in some way I can't imagine it if it doesn't if you've never seen something that looks like this and your visual eye and you're dealing with survey data I want to see what your data looks like because then it's different than anything I've seen in the last eight years in any case you see that a row number two over there you've got the the IDs for each question up at the time this is T 1 this is Q 2 this is Q 3 and you've got this actually useful thing here don't just discard it we're going to talk about it we're not going to throw any parts away or for the most part in dealing with this also I've conveniently set up my questions everything in here so that my then my so-called demographic questions I put in the front like I want might want to filter a show what's the difference in responses from men versus women what's the difference in responses for people in South America verse North America difference in opinion and generation and since a lot of people have asked about this how do you do waiting in a survey and so I have another column in here which is there's a weight that's applied to each survey respondent and that's the way I usually see it done but it can also be done where you can gee anyone that's in you can in tableau say anyone that falls into this category give them this weighting anyone that goes into this category give them this weighting usually what happens in most surveys you get the critical demographic questions at the very beginning of the survey like are you alive yes or no etcetera but you don't want to bludgeon people with too many questions yeah so you'll you'll have a thing which is like who's your favorite Beatle or something like that at the end of the survey but you're going to need to if you're using the Excel free reshaping tool need to put all the things that you're going to cut the data by at the front you're using something like all tricks they can go anyplace so I'm going to store it just by taking this stuff I'm going to create a new tab here I'm going to call it demographics and I'm going to paste this stuff in and I do not need this blank row however this stuff is very useful for the questions that I may be analyzing q1o that's pulse rate Q 2 is metabolism I don't like having to right-click and alias everything so I want this stuff so I'm going to show you what I'm going to do and I think a lot of people already have this yeah I'll just take these two things copy this and B paste and let me transpose the data and it's only demographic stuff and this will be my question ID and this will be my wording and if you have never done this before you're going to really like how this is going to make your life easier see there we go and do one more thing here so I've got my question ID the human readable form of the question and then I'm going to put a grouping by the way I need you to hang with me for a little bit as I set the data up right I want to show you Wow look at this cool way to visualize this this or this but this is something I haven't blogged about I think I'm seeing nodding heads and a bunch of people put it in and kind of figured out how to do it so this is a question about voting this is a question about salary here are all these questions these are all check all questions and obviously you may have different sets so check all that apply about what you measure check all that apply things that you buy etc and these are all Likert scale questions and it may you may have a really hip survey system that generates this for you automatically and I'm happy for you I don't wear none of my clients do yet and so I'll take a little bit of time and I will do this takes 5-10 minutes it's a little piece of scab holding or I just call it my question helper all right so I've got my demographics here I've got my helper stuff here now we got to get down to the brass tacks of this thing and we've got to reshape the data and Mark reader was showing you that and this cool ability that's going to be in Excel and we'll take all these things that are all these millions of columns and not so many rows and turn it into not so many columns and lots of rows that's the crucial part to all this so let me get rid of this stuff and I'm going to go right to this location so I need the response ID but I don't want to have a separate column for every question that's in the survey you probably had three four 500 of these things you will be miserable attempting to visualise survey data if you get it in this format and this is the way that it comes when you say I want to download a CSV or I want to do this that or the other so how many of you have discovered or know about the free reshaping tool the Excel add-in that you're probably discovering yeah but it doesn't work on the Macintosh or a set of stuff like that etc great little facility has helped me out got me through five six years of pain and suffering I loved it I no longer use it but I feel obligated to show it to you because I'm not going to say because there there there are alternatives but they cost something so I'm going to reshape this data so instead of having I don't know how many columns I've got 30 or 40 I'm just going to have three and it created over 8,000 rows and I'm going to call this one I question ID and this is going to be the text response yeah the demographic stuff no harm in having them it's just adding additional rows also I purposely put Generation gender location but I may want to promote one of these questions like do you want to vote do you plan to vote in the next election maybe I want that to be a question I analyze and a way that I want to cut the data so I may have it live in both places both as I want to cut the data this way or I want it to just be one of all these questions so this is good I know you're going wait a minute I fish I thought you said text response but that's clearly a number well that was please enter your salary and that's never going to be you know that's that's a number they enter it's not going to be pretty big as something that somebody types in so it text where there is text values where there are only numeric values where there are numeric values I've got to do the same thing here by the way the reason I'm not a big fan of doing this is one it's tedious to it's error-prone three more often than not I get those check all that apply questions where it's a one or a blank versus a 1 and a 0 so I have to do all sorts of stuff in here and if I were Lewis black you know we've really delivered that the thing that really annoys me is when the client says yo you have a survey is closed and you've done all this stuff and it's you know take an hour an hour and a half you got everything just right if you have to deal with their sloppily coded stuff you put in all these calculations let's say if everything is blank cool otherwise put zeros in so that I know that they answered at least one of the check all that apply they come back and go you know what we really wanted to get those additional responses from Latvia and the survey you know so can we add these 50 additional responses etc so it's one this is an it works you'll get the data the way that you want but it is error-prone it is tedious and it doesn't allow for I want to start visualizing the survey when it's halfway done I want to get a jump on this thing I want to see if there any trends or stuff that we should be looking for early on so let me go through one more step with this we're just about done let me go here let me reshape the data and you see what I'm going to do here this is just the values and where the the veils by the way the person who's presenting immediately becomes the worst typist in in you know in the room it's just that it's just a given so what I'm going to do here copy this and let me paste this in here so I got my text response and let me get my numeric response so zeros and ones instead of yes no maybes etc and hold on let's see if everything lines up ah that feels so good everything the same number of responses on the tape you know how many times you've seen oh crap there are two extra ones down at the bottom I hate when that happens and I will just call this you know merged and let me save this under a different name and so I got my demographics I've got my helper file and I've got both my respondent ID the question ID the text response and the numeric values can make my life way easier because sometimes I need numeric stuff sometimes I need tech stuff and for years I have been using this tool and I would now go into below and connect to this thing and II caught grieve who is is a brilliant tableau Ian presenter kind of Zen master cat herder I had to run the session through him he said why aren't you clicking the openness in tableau thing that that I lobbied to put in the tool years ago I said good point so let me just click on open in tableau it is amazing the stuff that how long is how long is that incredibly useful feature that I didn't know about been in the product lookup for years or stuff like that yikes so in any case we're set I'm connected to the right data set everything is fine I'm going to start by taking my demographics then i'm going to take my merged with that has the reshaped data from the with the text reshape data with the point people are good I'm just going by yes we know this move okay and I just love this it knows that I'm joining this on response ID and now let me get my also an ever so important helper sheet I only started doing this about a year ago and it's made a huge difference in fact certain things that solve and it's mapping the question ID to the human language readable version of the question ID and I will tell you this though I am still in the habit of creating an extract TDE file I've just had too many times that I've been burned connecting directly to Excel they put all this effort into getting rid of the jet engine and allowing you to do stuff with Excel I just find better performance with creating a TD file question here Elizabeth right I couldn't you know what I could have done the join on that and then gotten rid of the fields that I didn't want etc absolutely but so did I the question for those who are it's how many months and somebody's listening in on the session could I have done that you know instead of copying and pasting those things done a type of join and gotten it that way and the answer is yes and that's kind of the way I do when I do it in all tricks in any case let's have a create a TDE file I'll go to the worksheet I will call the TD file something somewhat less cryptic actually it's not cryptic at all it's the name of the file and it's the name of the sheep or some stuff like that and I've got everything that I want and I'm seeing that I've gotten these things from three different tables I tend to prefer to I prefer using group by folder because really there's it's I don't need to know that these came from this table and this came from this table it also means if I want to create calculated fields and group them together by function not by where they came from it's easier to do so all right so almost always in fact no not almost always always first thing I do is I want to know who my respondents are before I start analyzing you know who answered these questions so I'll put gender up on rows and much to my delight I have male female and not know or unknown or things like that inside okay good that's data that doesn't need to be cleaned up by the way tableaus wait at helping you discover where where the nasty bits are in the data and what I need to do here is then count the unique number of respondents something I didn't dwell on for a second is when I did this reshaping of the data is wait a second you mean you've got a separate row now for every respondent but for a separate row for each respondent for each question they answered yes so if I were to just say I want to count the number of records some the number of records up or something like that I'm going to get way more people than there should be or if I wanted to just count that so this is the case where I need to count the distinct number of respondents so I've got the respondent ID by the way if I didn't have a respondent ID I would have put one in there if somehow I gotten it I have yet to see that there's always some type of unique identifier for each of the response from any grown up survey system and I've got the information here I promise I will come back and say well how about if I want to know what the weighted number is on this thing how would I do something like that it's a little bit more complicated so I've got gender here and I might show the results or I might do a quick table calculation say percent of total and I might duplicate this and instead of having mail I will do put generation in here and see well what have I got and I'm going to do the Julia Child routine more and more people have no idea what I'm talking about say the Julia Child routine but she had an extremely popular show on PBS the French chef and where she would show okay we're gonna bake the souffle and she put it in one of --n and then she'd go to another oven and take the fully baked souffle out of the second oven so rather than walk you through this I've created something for generation I've created gender breakdown by location and then I will sometimes build an interactive dashboard out of this and I will put an end cont in the upper right corner and if I want to know how many female Millennials from North America are there okay I've got 25 I can see what the what the breakdown is question Owen ah okay so the question that I have is in the upper right hand corner there's this running end it's saying how many people are responding I got to show you another way to do this as well because it's it's cool the this packaged workbook which probably comprises 90% of what I know about survey visualizing survey data that's be available for you to download and look at and go wow that's cool or oh that's a really stupid way to do it I have a better way to do it which I'm happy for you to tell me about so this will be readily available for you to download and look at this so if you want to do the reverse engineering thing and we see well you know I've got a floating element here I go to the sheet there's this field that I called n it just has n equal in it and I've got this other I will use this bogus field called color so that I can drag it over the color button and then use one of my predefined color palettes as opposed to not having anything on color clicking color and not having the the list of color palettes there if I do that it just allows me to get it it's just a little bit of a trick and I'm just hiding the headers actually I have the head over here so I don't get this extra crap up at the top etc and I just got action filter so it works very nicely what I was going to show you and might as well show you now is I've got percentages here but if let's say I want to do a count of response ID sorry I'm going to put count of receive response ID over here I'm going to make this discreet and if you take something that normally wants to be a measure and say make it discreet I can now at any point in this survey for the most part be able to see well what's my end for this what's my end for this what's my end for this what's my end for this so I'll show you that a little bit of that later and just in case we get make this a packaged workbook and how convenience is actually saving and in the right place as opposed to wherever I just happen to open something from it's a little gripe so getting back to this item um it's more for me my clients seem to enjoy it but it helps me get a sense of who are they how many people are there are we going to run into a problem where the end gets really tiny if we're looking for left-handed Latvians from you know from a matter of female or whatever oh we've only got an f3 or something like that it helps you see where where things get our thick and thin amongst your respondent count well let's go to our next question that we have going the first real question that we have in the thing which is do you plan to vote in the upcoming election so I'm going to start by putting my little question helper up here which has the word the grouping that I have so I have four different groups of questions here I have my Likert scale questions I have my salary question my do I plan to vote just to show you how that might work if I could say gee I just want to do the check all questions and then if I then put the question ID up here you don't know which of the questions because I said I don't have to have this massive thing like this and say oh I want this question in this question in this question in this question so instead I can do it by by that grouping that we put together and again I'll do it with the check all question and instead of putting Q 1 Q 2 Q 3 Q 4 and saying what is Q 1 it's this what is Q 2 I can instead take the wording that I created in that little helper file and I've got all my stuff there that I haven't written about or blogged about huge time-saver and if you have one of those dual type of questions where indicate to the degree in which of these services how important are these following services and you have 20 things then you've got the how satisfied are you with these 20 things if you have that wording thing the wording will be the same in both places but you've got the question ID differently and you've got the grouping is these were the satisfaction questions and these were the what do you how important questions super easy to line them up so doing this little helper file makes a giant difference in any case I want to go to the vote question and let's get our responses in and so I'll reach sort them yes/no don't know and let's get well what were how many people said yes how many people said no how many people said don't know turns out I can take number of records or count response ID and the or count D response ID they're all going to give me the same answer in this case because we are filtering by this one question we are not getting the ID is repeated for every question we're just dealing with that one part facet of this so this is going to give you the same result each time let me go ahead and change this to a quick calculation percent of total there's a reason I'm not doing the default format for it because sometimes I need it with decimal places sometimes without and I've kind of got a pretty good sense of this I'm well we now know do you plan to vote in the upcoming election yes no don't know suppose I want to know well how do the responses differ from men and women by location by generation etc first blush you might think well let me put gender up here let me be ever so efficient and right-click on gender and say show quick filter I hate that term quick filter and it's like what is the other filters slow it's it's a little bit confusing and then the problem is if let's say we make this a radio button and I look at our right here responses from women here are responses from men or if I do this I do the same thing it's kind of hard to remember back and forth you know which you know you have to remember what were the you know what was it for men what was it for women etc so I it this is useful if your user is only interested in seeing responses from Generation X and they don't care about stuff if they however want to compare how Generation X compares some Baniya list to Baby Boomers etc this is not a great way to do it and instead what I would recommend doing is well let me break this down by gender now I now need to change the scope of this table calculation so it is doing it by text response and then it this may make it a little easier to understand how the scope of it is I am addressing by the text response and I am partitioning it by gender so that it restarts again whenever we get to the gender so I've got this going on here and now I can see oh yeah there's kind of an interesting difference between men and women here well now I probably want to do the same thing and see well what happens when I put do it by location what happens when I do it by Generation now I'm a efficient and lazy developer and I don't want to create three separate visualizations for this so what would be a cool way to allow someone to explore the three different ways of cutting the data thank you create a pretendin have we ever met before whomever just said parameter no we have not thank you Bravo we're going to create a parameter so let me do that on the fly quickly and create parameter and I will call this I don't know if I would call it drill down died down by or breakdown by but I'll call it breakdown I haven't decided what's really right and and if the value is zero I'm going to give people four choices I don't want to break it down by anything if it's one break it down by gender and this is what's being displayed if it's to break it down by generation three break this down by location and parameter control excellent and now let's create a lovely little calculated field here called breakdown by and you can either be a case when person or an if/then person it doesn't matter if then allows you to say greater than or equal to K Sony works with equal signs I'll say if breakdown equals zero then I don't want to see broken down at all no string by the way the reason I'm doing this as equals zero equals one equals two equals three equals four one is that's kind of what I'm in the mood for - I've heard performance is like over the course of a million years it will save you five percent of five minutes of computing processing time to do it this way the other is sometimes you have these really long strings if it's and what you allow them to select from the parameter is some long string and you end up trying to copy and paste or you have typos this is just a little easier and I'll see if breakdown equals one then we want to do this by gender else--if breakdown equals two then a generation thank you then I could have said else if located but maybe I may end up putting more in here and realize we can argue about what would be the ideal syntax so I'm about to break something when I say break down by put this here oh that Joey's working well and I'll say break down by gender where now I want to break it down by generation where now I want to break it down by location very clean one item really easy to see etcetera yes mm-hmm one zero one two or three what what I did was I created a parameter that would have a value of either zero one two or three because I knew that there was a nun there was a generation there was by this and there was by this and and it worked by the way do not and I mean do not let me get away without showing you the how to do the reshaping and all tricks I'm going to I'm not going to go through every visualization but I want to go back to all tricks in a moment in any case this is cool but we want to show you something anyone from Deloitte here by any chance did some work with Deloitte on their human capital trends report I am NOT responsible for the colors okay they made me use these colors but we're trying to show and we're going to get into this divergent stacked bar chart and the question was how urgent are these things in your organization and where we're leadership is is a great concern and diversity and inclusion somewhat less and they wanted to be able to drill down by different items tell me your eyes didn't go immediately to the stuff in orange what we want what was happening is there were stories that were in here where there were big differences between different response groups but it was hard to find them so I wanted something a way that would say if the difference between one group of respondents and another was greater than 10 percentage points flag it put in a different color you could also say if it's statistically significant put it in a different color so I want to come back to that and and so this kind of came out of a need we're dealing with some clients and I've got it got this going on here where there may be some pretty significant differences between baby you know Millennials and traditionalists between or over here between traditionalists and Millennials 40% first 57% maybe I want something that drives at home so let me get into how to do that but let me do one other question type and all which is to check all that apply question and let me show you how to do it with weighted values because I think that's been a kind of a hot button for people so let's drag our grouping over here I'm going to do my check all questions let me put the wording of the questions and I've got to create a calculated field and I'm going to go with values and I'll call this check all percent now what are the IQ I'll do it this way just for clarity if the value equals one then one else zero and so it's going to just check in thank you but I'm going to have it iterate through each of these items and add them up so it's going this person say one cool this person say one cool this person said this one didn't okay suit and it will add them all up so this would give you a count of everyone that said they wanted they measure this particular thing which things do you measure metabolic rate pupil dilation etc then I'm going to divide this by some of number of Records this is the correct way to get a percentage check all that apply question now it turns out value is either 0 or 1 I could just say sum of values because it would just add everything up we'll add the ones and not the zeros I'll explain why I'm doing this later click OK let me take my check all % put it right here sort show the values and change this change the default format to this to be a percentage zero decimal places and we can now see what it is the people are measuring and not measuring and it's pretty pretty clear if I add our breakdown by I get I start to see some pretty cool stories here so I'm going to switch back to the finished version of this what do you measure check all that apply oh you know well let me show you how to do the weighted value on it again you're going to look at the you're going to have this readily available to you so here's my check all that apply questions with and here's how you do a weighted if the value equals one meaning they checked it it's either a zero or one if they said or if it was if the text response equals yes then put what they're waiting is else zero and then divide it by the sum of all weights and that's how you would get the weighted response here so that's how you do that it's not terribly difficult this is what I wanted to show you which is in the previous screen it's a little hard to see that there's a story in here there's a place where there's a big difference between what men and women have to say about this so I wanted something that flagged this you cannot help but go oh there's something going on there and if you were really hip you would say oh I'll parameterize it if it's within 5% if it's within 10 percent within 15 percent I'm going to show you a scary-looking table Kalp don't don't don't worry you can see that I'm coloring this by a field called major differences and if I look at this all this is saying is look within this set of responses for this question and if the biggest value - the smallest value is greater than or equal to 10% then this warrants consideration if that's the case that's this next stop appear if this value is either the minimum or the maximum color it as a major difference otherwise color it as a minor difference and that's what's making the thing kick out and so that's how we're doing that one more item and then I'm going to shift back to all tricks and I could easily go you know two hours on this stuff the how many of you said you know what let me grab a sandwich before I you know grab a little bite on the go there's this were you also one that said parameter this is a smart woman and you should you've got a hot lunch okay make everyone feel you know I'm all right with the good let me I went years without having to do the following item how do people rank certain products certain songs based by different categories Oh people in the Northeast how they rank these 10 items how do people in the southwest rank these 10 items and then suddenly a spate of clients all needed to do this and if you were to type into Google ranking within a category tableau you will find a lot of knowledge base articles about how to do it if you're using a tableau prior to 8.0 and earlier you've got to do some nested table calculations if you're using 8.1 or later there's a ranking function it's really cool and I did something for Deloitte and maybe a month after they published this I got something so much better so here are these top five trends sorted by work region and if I hover over this I can see workforce capability is number one in Africa it's number two in Asia it's number two in central it's number five in Latin South America you can see leadership is is you know top or top two throughout etc the my problem with this is one it's a hard read - I can't get everything in and I actually had 12 trends not just five I took the top five so this is this next thing I'm going to show you is pretty darn cool at least I think it is visual ranking within a category and let me do this by generation the trick is you cannot do this in one visualization it requires interactivity but what I'm doing here is I can click on an item and see well how do these things rank adrenaline production metabolism blood pressure and an huge story right there that's coming out and it's really easy to see all the items and pops out traditionalists only measured this 27% it's right fifth versus baby boomers and Generation X and breathing huge difference by the way do you know why there is such a large difference in here between traditionalists and baby boomers etc because I rigged the data so I could say look how wonderful this is for finding where the cool story is and your data but I did want to have that I'm not going to get into how to do it there's a full blog post on it this is proving to be a killer way to show ranking within visual ranking within a category and it's kind of the go-to way to do this I'll show you one or two more things and then where to find the additional items but I do need to show you I don't use the Excel add-in anymore I'm using all tricks to do all this and how many of you you know have looked at all tricks and you see something like this and it looks somewhat intimidating I'm I certainly felt that way this is just the workflow all the things that I just did I take this I move it here I take this I move it here etc presented with icons and and arrows that are pointing left-right etc the if I were to if I were to put a diagram up explaining what I do in the morning to get dressed and go out for work it would be ten times more complicated than this diagram it really isn't that tough let me just show you what what I'm working through this says I want to open I want to get it to a data source in this case it's an excel file this icon the filter icon or the Pink Floyd Dark Side of the Moon icon is just saying oh I have this extra row in there don't include that extra row that thing that had response ID under response ID this is what I'm using to create the demographics portion so I'm saying just include these fields gender location generation and weight if I decided I want this question to also be a demo graphic question that's all I have to do similar thing here I open the data source Oh same thing same data source for the labels and here's where I'm saying transpose the data take response ID and then everything else and transpose it and so that the first column will say respondent ID the next column I'll call it question ID and the next column I call it label so it isn't difficult to do this I'm doing all the joins in here and then it can either spit out we can spit out anything it spit out a CSV file and Excel file an SQL database file or or the icing on the cake it will make a TDE file and I'm going to run this process so we just did all the conversions and I just want to show you what the file looks like so here it is everything everything joined together I've got the response ID the gender the location the values etc way much happier using this tool if I get new data from a client no problem I just put it in and the same thing I'm using for excel I could do this with and connect it directly to a database as Mike Neely here any chance he may be presenting he's with Ingersoll Rand he's doing stuff with Qualtrics where he's connecting directly to Qualtrics he's not downloading stuff or anything like that and he can just rock and roll it so it's great much better way to approach this so I just want to show you one more vis type and then where some of the cool stuff is where you can find and where we can move from here so let me get to I want to look at people's salary I want to show you how to do a jitter plot because it's an incredibly easy and it's proving to be a really go to visual is Asian type so let me go to the let's bake it up ourselves I'll put our grouping and I just want to look at the salary question and I want to see the average salary broken down by gender and you can see that on average men make more than women in this particular dataset well let me show you how you can make this thing a little more interesting and then how you can do like a benchmarking interactive survey with it so instead of this being average overall I'm going to put every respondent ID on the level of detail so I in fact have a separate bar for each respondent that's here let me change this to the circle and with your permission I'm just going to flip it this way well it's kind of hard to get a lot of meaning out of this because you have this mark on top of this mark on top of this mark so you'll try the jeep you know maybe I'll make the color semi-transparent etc and it's just not working that well so what I'm going to do is create a calculated field called I'll call it jitter and it is just a table calculation that says what row am i in in the partition and I'm going to put jitter on columns but I'm going to compute using the respondent ID move this over this way and now I can put median lines max man etc there are a few things I do to make this a little let me show you it out in the field so this is proven to be an extremely popular this has been downloaded a lot of times where I have survey responses from 2398 people and each one of those dots is a different respondent so I can hover over dot and get that information and then I'm just using trend lines that get 95th percentile up or quartile etc I can now type in my own salary and I can see where am I within this grouping and gee let me see where I am within males etc someone challenged me how dare they few things here one is and you can read about on the blog post and here's the workbook and it will tell you where to find it when I do the jitter with this index function here that is a modulus remember modular arithmetic goal like the number 7 and 2 or 7 and 12 both are equal mod 5 because when you divide them by 5 they have a remainder of 2 this is saying divide whatever row the person is in by 11 and and and put that person in one of 11 different slots reason I'm doing this this is we're getting into a little stuff but you know how if you see how queer it is to be able to read the bands I've kind of narrowed the jitter so that I've got this breathing room on the left and right of it and it gives me a lot more control it's also done in case your jitter isn't random enough so that like the IDs the lower IDs may have been lower values and the higher IDs or higher salaries and you don't get this thing which is random this is a way to say put this thing into one of eleven slots in any case the I what I did thank you is I fixed the scale here let's go to show the header of this edit the axis and I went from minus 3 to 13 instead of the automatic which wasn't it was a little too crammed for my taste so here was one of the challenges I had which was going to show a different icon depending on the person salary so you know gee you know I'm in the lower quartile I'm sad NL EMA let me change this to uh oh you know I'm above the medium I'm happy and you know a mr. monopoly right now so I'm not recommending you do that but you can dissect the item I am going over so I'm going to take two minutes to show you stuff and I want to leave some time for questions there are only two other types of things that I've got going on and I have one of them I've blogged about extensively one is the Likert scale questions and where to what degree do you agree or disagree with the following statements and I've got both unweighted and weighted versions of these things and I am convinced that people have gone with a just show me the percent of people who selected the top two boxes because you just can't make any sense out of this thing but if you say well let me show you an improvement on it here's one way of doing it I've got a dual axis chart here and so I've got the not at all small degree moderate high degree very high degree and then superimposed is this percentage that said top end boxes we spine percentage top end boxes here we go and I'm just saying if I were to hard-code it I would say if the value is greater than or equal to four then yeah they're one of the people that said top two boxes else zero then divide by the number of people who responded what I like about this is I can say show me the percent that said the top box top two boxes top three boxes this is the way I think people should be showing sentiment of any sort how much do I like a candidate etc broken down in various ways it's either called the staggered or divergent stacked bar chart and I've written a lot about it someone said how do you do it with weightings on a forum post here's the answer to it so you just click there and get it and then I developed am oh let's do this a soup to nuts kind of every way that you might want to do this no breakdown show me the Likert score versus the percentages hide the neutrals Jeff show positives and negatives etc let me break it down by generation and I got a highlighting here and showing where there's a big difference that's going in the last item and this just came up forum post someone wanted to see how do I compare how people responded to this question with how they responded to this question and here it is that's you know like by 800 this is exactly what you need oh you're going to love this the the this is meaningless because I've got so many responses on top so it's how do i jitter this okay hey you made it the how do I get er this thing and get this thing and I huge surprise I was not expecting this I do not like to pack bubble charts okay I don't think they tell have any there it's because we're terrible at judging the valley area of circles if I were to show you two bars one which is twice as large as the other you go that's twice as large if I were to tell you here are two circles how much bigger is this circle and the other you go I don't know but that one's bigger that's the only extent that you can do it so watch what happens with this chart and I've got it that I just made it that I can choose any two particular questions and compare one with the other I'm going to make instead of this being continuous I'm going to say make it discrete I'm going to say make this one discrete and we're already starting to get some pretty cool things happening here then if I sized this by the number of respondents for each one count County it doesn't matter and let me just change the order of this so that's going from and it's like wow PAC bubbles that are useful and I can see yeah there's a really clear correlation group but if this were a question about how important is something and how satisfied are you you want to see high importance high satisfaction if you're seeing very important and there's a cluster of groups that are saying and I'm very unsatisfied I want to highlight that group and see who they are and I can now do that really easily but you know the this was like you know a huge wonderful surprise wasn't expecting this I can break it down agender this is where I was going with stuff I thought I was going to have to come up with some sort of and and and did all sort of stuff and it was nasty and jittered this stuff and it was complicated and I made the only thing I don't get with this version is because it is I've only got discrete elements on rows and columns I can't get a trendline if I need the trendline I got to go all fancy on stuff or I could just do this this is exactly the same as this so let me wrap up with some stuff where do you find all these goodies so I've kind of walked through I'm making this presentation available to everybody you can download it let me show you two items I'm not trying to shell for the website but everything is going to be available at data revelations comm slash visualizing - survey - data the packaged workbook the the PowerPoint presentation the ultrix module the raw data the reshaped data everything and then hoping to have it by the end of the week beginning of next week questions were concerns or ideas you can there you that's where you can download tableaus white papers I leave that up for another second or so okay because some have low person's going to be really angry with me if I don't go to the next slide which has the slide that you see at the end of every presentation which is please take the three you know the survey thing oh let me let me bring this one up and if you need that I'll bring the other one up so if he can give feedback etc I realize people are starved I've gone over a little bit thank you thank you

Channel: Steve Wexler

Views: 101,907

Rating: 4.8912134 out of 5

Keywords: Tableau, Data Revelations, Steve Wexler, Surveys, Survey data

Id: MbTKbghfzcA

Length: 67min 30sec (4050 seconds)

Published: Fri Mar 13 2015