Which Excel sheet should we be looking at for the raw data?
Thanks for the quick response, Ken!
The first sheet in the Excel file, labeled "Standardized Data Set" is the raw data. The subsequent tabs show the data transformation process.
When I connect to that sheet, I get this:
The columns don't have names so it just creates some for us. I'm going to rename them 1, 2, 3, 4, 5.
Then I'll pivot these columns (here's how: 3 Ways to Pivot Data for Tableau - The Flerlage Twins: Analytics, Data Visualization, and Tableau ). That will give us this:
I'll rename the fields and change them both to numbers.
Then we can join it to your matrix of descriptions.
After some cleanup and hiding a few fields, we have this:
From here, I'm not 100% sure how your chart is supposed to work, but I created something like this:
But, hopefully, this is enough to get you moving in the right direction.
Survey.twbx 29.2 KB
Thank you for the walk-through tutorial here, Ken.
One follow up question: when pivoting in Tableau, you kind of only have one shot at it. You are able to Add Data to a Pivot but by pivoting large data sets and subsequently adding to them, all of your Pivot Name Fields and Pivot Values end up combined into the two aggregate fields regardless of raw data or how you want to work with it. For example, if I have more survey questions in the example raw data above, it's likely that these additional questions will be unrelated but still might require a pivot step, which will then combine fields and values with the existing pivot data. Generally speaking, when working with combined pivot data do you tend to create Calculated Fields or event Sets to "break apart" like data into categorical sets or do you simply use the Pivot calcs as filters to select / deselect the data you need from worksheet-to-worksheet? I'm sure it depends on use but I'm just curious if there's a general best practice or rule of thumb to follow.
Thanks for your time!
You are correct that, in Desktop, you get one pivot. It would be great if you could create multiple pivots for different types of fields, but that's not possible. So, as you've noted, you end up including all these different types of fields in a single pivot then using a bunch of other techniques to break them out. While that's doable, I usually recommend that you look at doing some data prep ahead of time. Tableau Prep, for example, will allow you to perform as many different pivots as you like. So, if Prep is available to you, that would be a great option.
Thank you for the advice, Ken. I will look into using Tableau Prep ahead working with the survey data in Desktop.