6 Replies Latest reply on May 26, 2020 7:30 AM by matt hins

    Survey Data - Rank Order Question

    matt hins

      Hi, I am trying to figure out best approach to visualizing rank order survey data as a frequency bar graph. I can pivot the data in Excel to setup a frequency table that is easy to use in Tableau but I'm wondering if there's a way to achieve the same thing in Tableau without first manipulating in Excel. I've attached an example of the processing steps in Excel to get from raw survey response data to frequency table and the resulting Tableau viz. I am trying to repeat this process with a much larger survey dataset and I would like to keep the whole process within Tableau. I know there's a way, I'm just not certain of best approach (nested sort, COUNT IF calc, LOD calc, etc). Any advice would be super helpful.


      Thank you!

        • 1. Re: Survey Data - Rank Order Question
          Ken Flerlage

          Which Excel sheet should we be looking at for the raw data?

          • 2. Re: Survey Data - Rank Order Question
            matt hins

            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.

            • 3. Re: Survey Data - Rank Order Question
              Ken Flerlage

              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.

              1 of 1 people found this helpful
              • 4. Re: Survey Data - Rank Order Question
                matt hins

                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!

                • 5. Re: Survey Data - Rank Order Question
                  Ken Flerlage

                  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.

                  • 6. Re: Survey Data - Rank Order Question
                    matt hins

                    Thank you for the advice, Ken. I will look into using Tableau Prep ahead working with the survey data in Desktop.


                    Thanks again!