-
1. Re: How to format the source data
Richard Leeke Oct 27, 2011 10:56 PM (in response to Ben Watanabe)Structure your data as attached.
-
sampledata_0_rl.xlsx 21.3 KB
-
example_rl.twbx 21.1 KB
-
-
2. Re: How to format the source data
Ben Watanabe Oct 28, 2011 9:01 AM (in response to 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 Oct 28, 2011 1:36 PM (in response to Ben Watanabe)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.
-
example_15_rl.twbx 18.3 KB
-
-
4. Re: How to format the source data
Ben Watanabe Oct 30, 2011 10:41 PM (in response to 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
-
sample.jpg 22.6 KB
-
-
5. Re: How to format the source data
Richard Leeke Oct 31, 2011 1:00 AM (in response to Ben Watanabe)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 Oct 31, 2011 5:34 AM (in response to 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 Oct 31, 2011 7:10 AM (in response to Ben Watanabe)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 Nov 1, 2011 7:49 PM (in response to 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 Nov 3, 2011 8:48 PM (in response to 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
-
example.twbx 73.4 KB
-
-
10. Re: How to format the source data
Richard Leeke Nov 4, 2011 7:54 PM (in response to Ben Watanabe)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.
-
example_17_rl.twbx 105.6 KB
-
-
11. Re: How to format the source data
Richard Leeke Nov 4, 2011 10:22 PM (in response to Ben Watanabe)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.
-
example_17_rl_edit.twbx 115.2 KB
-
-
12. Re: How to format the source data
Ben Watanabe Nov 6, 2011 8:06 PM (in response to 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 Nov 9, 2011 6:52 PM (in response to Ben Watanabe)Attached is what I was thinking of for a good view of this kind of data.
-
example_17_jm_edit.twbx 145.0 KB
-
-
14. Re: How to format the source data
Ben Watanabe Nov 15, 2011 11:38 AM (in response to Ben Watanabe)Richard,
How did you get the bar charts to have both bars relative to zero instead of stacked and combined together? Thanks