1 2 Previous Next 27 Replies Latest reply on Dec 3, 2011 1:16 PM by Ben Watanabe

    How to format the source data

    Ben Watanabe

      I am trying to get the workbook to look like the picture with the questions on the side and the rating across the top. It wont have to display more than one location at a time so the location will always be a filter. To get the question on the side, is that a formatting thing in Tableau or do I have to change how my data is structured?  Thanks

        • 1. Re: How to format the source data
          Richard Leeke

          Structure your data as attached.

          • 2. Re: How to format the source data
            Ben Watanabe

            Thanks Richard,

             

            I was hoping to avoid having to reformat the data that much but I guess I don't have a choice.  Thanks

             

            Ben

            • 3. Re: How to format the source data
              Richard Leeke

              You can actually achieve the same structure as far as Tableau is concerned without physically changing your source data, by using a custom SQL connection.  That would get quite messy if you have lots of questions, though.

               

              I've attached a copy of your original workbook with a custom SQL connection to your original workbook showing how to do that.  The custom SQL basically just picks each question out from each row and turns them into separate rows.  It looks like this:

               

              SELECT ['Survey 1$'].[Location Code] AS [Location Code],

                'Question 1' AS [Question],

                ['Survey 1$'].[Question 1] AS [Answer]

              FROM ['Survey 1$']

              UNION ALL

              SELECT ['Survey 1$'].[Location Code] AS [Location Code],

                'Question 2' AS [Question],

                ['Survey 1$'].[Question 2] AS [Answer]

              FROM ['Survey 1$']

              UNION ALL

              SELECT ['Survey 1$'].[Location Code] AS [Location Code],

                'Question 3' AS [Question],

                ['Survey 1$'].[Question 3] AS [Answer]

              FROM ['Survey 1$']

              UNION ALL

              SELECT ['Survey 1$'].[Location Code] AS [Location Code],

                'Question 4' AS [Question],

                ['Survey 1$'].[Question 4] AS [Answer]

              FROM ['Survey 1$']

               

               

              The custom SQL would start getting quite long if your real data has lots of questions (i.e. is very wide) - I know you can run into a limit in the MS JET database engine used for connecting to spreadsheets if you have too many clauses to UNION together.  I vaguely remember someone posting to say they had hit the limit at around 70 or 80 - but then posting back to say that they had found a workaround which involved breaking it into chunks and nesting them.

               

              It really depends how much pain physically restructuring your original data source would cause.

              • 4. Re: How to format the source data
                Ben Watanabe

                Hey Richard,

                 

                I decided to use the first option, I found a way to change all the excel sheets quickly.  I do have another question for you when you get a chance.  How do I make all the rows appear even if no one chose that answer?  I am using a quick calculation so that each row i get a percentage of how many chose each answer.  I would like even the rows with 0% to show.  Is that possible?  Thanks

                 

                Ben

                • 5. Re: How to format the source data
                  Richard Leeke

                  I presume you mean columns where nobody chose that answer, do you?

                   

                  The simplest thing you can do is un-check the Hide Empty Columns option under the Table menu.  That will display all columns (i.e. all values which exist for any question for any location) even when you have filters set which mean there aren't any entries for that column.

                   

                  Bear in mind that Tableau can only show columns (answers) that it knows about - so if there was a possible answer which nobody chose at any location, Tableau won't know about it and can't show it.  If you think about it, there's an infinite set of possible answers which nobody chose - "Who Cares" being the typical one that I always want to answer to all the questions on nearly every survey that gets stuck under my nose.  ;-)

                   

                  That will give you the columns, but it won't display 0% - because there actually isn't a value there at all.  If you really, really want to display 0% that's harder - it almost certainly means you have to mess with the structure of your data source again.  Post back if you do want that - and we'll see what Joe can come up with.

                  • 6. Re: How to format the source data
                    Ben Watanabe

                    Hey Richard,

                     

                    Thanks, yeah I saw that in the menu right after i posted.  As for the 0%, if there is no easy way then I guess I will have to make do.

                     

                    On a side note, is there anyway to insert static data into the table?  Say if you wanted to have a column of percentages right next to each current column that didn't change and was used only for comparison like a benchmark.  I guess you would have to create dummy data right?  Thanks

                     

                    Ben

                    • 7. Re: How to format the source data
                      Joe Mako

                      If you are interested in making a visual display of survey data like this, check out the resources at http://www.datarevelations.com/category/vizbizwiz-blog

                      • 8. Re: How to format the source data
                        Ben Watanabe

                        Thanks Joe,

                         

                        I tried it but it doesn't work in this case since its not that the data is null as much as it doesn't exist at all.  So I need something that say "if you cant find x then set it to 0".  Thanks.

                         

                        Ben

                        • 9. Re: How to format the source data
                          Ben Watanabe

                          Thanks Richard,

                           

                          One more thing, so I have the table and it can be sorted by location to determine what percentage of all responses answered each question what way.  What would I have to do to add another column right next to each of those with the total across all locations?  For example: If I filter by location A and i can see that 53% of all the people that answered Q1 answered it as "Strongly Agree"  then right beside what I want to see how many answered "Strongly Agree" for Q1 across all locations as a kind of reference. Can I make a table calculation that escapes the filter for that?  Thanks

                          • 10. Re: How to format the source data
                            Richard Leeke

                            Yep, you can do that.  I couldn't think of a way to do it with filters, but you can achieve almost the same with a parameter and a calculated field.  The only drawback with that approach is that the parameter won't automatically pick up any new Centres that get added to your data.

                             

                            I added a second column with the overall answer.  I also added a graphical view of the same thing in the second sheet.  By locking the axis scale it makes it quite nice to spin through the Centres and see how the responses change.  Finally I added a view which compares all Centres with the overall score.

                            • 11. Re: How to format the source data
                              Richard Leeke

                              Thought about this again while I was out for a run.  I've added another view which does let you filter the Centres whilst still keeping the overall comparison, so you avoid the issue of the fixed parameter value list.  I've done it as a multi-select filter so you can compare one or more Centres to the overall results.

                              • 12. Re: How to format the source data
                                Ben Watanabe

                                Thanks so much Richard!

                                 

                                This is exactly what I was looking for.  I really appreciate all your help. 

                                 

                                Ben

                                • 13. Re: How to format the source data
                                  Joe Mako

                                  Attached is what I was thinking of for a good view of this kind of data.

                                  • 14. Re: How to format the source data
                                    Ben Watanabe

                                    Richard,

                                     

                                    How did you get the bar charts to have both bars relative to zero instead of stacked and combined together?  Thanks

                                    1 2 Previous Next