3 Replies Latest reply on Apr 8, 2017 8:06 AM by Okechukwu Ossai

    Need help creating a table

    P R

      Tableau 10.2 Mac

       

      I have an excel file of survey responses. If you look at the excel file on the summary tab, you will find a table called "Last Month"

       

      The "category" column is each survey rating 1-5. The "1" column is the first question, the "2" column is the 2nd question and so on.

       

      What the table is doing is capturing how many responses there were for each "category".

       

      So what Id like to do is a pick a month, then be able to see how many responses I got for each category for each question.

       

      For example: I filter on Dec 2016.

       

      The table now shows me, for each question, how many ratings of 1 there were, how many ratings of 2 there were and so on.

       

      I started building it in the workbook, sheet called "last month". Also included is the excel file.

       

      Thanks

        • 1. Re: Need help creating a table
          Okechukwu Ossai

          Hi P R,

           

          I've got a solution for you.

           

          1. Create calculated field [Year]. Right click on the field in the data pane and convert to dimension.

          YEAR([Date])

           

          2. Create calculated field [Month].

          DATENAME('month',[Date])

           

          3. Create a field called [Category]. Since some questions had no ratings, [Category] will have null values which you need to filter out while building your table.

          CASE [Ratings]

          WHEN 1 THEN "1"

          WHEN 2 THEN "2"

          WHEN 3 THEN "3"

          WHEN 4 THEN "4"

          WHEN 5 THEN "5"

          END

           

          4. Create a field called [Question ID]. This will make the Last Month table cleaner because the question headers are too long.

          CASE [Questions]

          WHEN "This is a friendly place to work" THEN 1

          WHEN "I get the resources and tools necessary to do my job" THEN 2

          WHEN "We all have the opportunity to receive special recognition" THEN 3

          WHEN "People are willing to make an extra effort to do the job" THEN 4

          WHEN "The leaders clearly communicate what they want to achieve (your expectations)" THEN 5

          WHEN "The company offers me training or other forms of training for my job growth" THEN 6

          WHEN "The chiefs recognize good work and extra effort" THEN 7

          WHEN "The chiefs involving people in decisions that affect their jobs or work environment" THEN 8

          WHEN "People here care about others" THEN 9

          WHEN "I am proud to tell others  that I work here" THEN 10

          WHEN "The chiefs show a sincere interest in me as a person, not just as an employee" THEN 11

          WHEN "This is a place where you enjoy working" THEN 12

          WHEN "The decisions of the heads ensure that we serve society with superior quality" THEN 13

          WHEN "I have the opportunity to express my view and ideas even if they are different from the leaders" THEN 14

          WHEN "I know what are my real priorities at work" THEN 15

          WHEN "All things considered, I would say this is an excellent place to work" THEN 16

          END

           

          5. Count the number of response for each question in each category for each month in a particular year. [Count of Response]

          { FIXED [Year], [Month], [Category], [Question ID]: SUM([Number of Records])}

           

          6. There are questions which didn't receive some ratings (category). So, Count of Response will be null for such questions. The null values are replaced with zero using this calculated field [Total Response Count]

          ZN(LOOKUP(SUM([Count of Response]),0))

           

          7. Build your table. See attached workbook.

           

          Let me know if this helps.

           

          Ossai

           

           

          Bear in mind that the Last Month table in your attached spreadsheet is calculating incorrectly for  some categories and question ids. This is because your Countif function is selecting inconsistent table ranges for different question ids. So, you are counting October 2015 responses for some categories and a mixture of February to October for other categories.

           

          For example, in your spreadsheet, response count for Question ID 8 and Category 2 is using =+COUNTIF('Raw Export Data'!M$184:M$453,Summary!$A$60) instead of =+COUNTIF('Raw Export Data'!M$172:M$184,Summary!$A$60). There are few other cases like that. If you correct all of them you will get a result similar to mine above.

          • 2. Re: Need help creating a table
            P R

            This is perfect! Thank you. And Thank you for spelling out the steps, so I can learn.

            1 of 1 people found this helpful
            • 3. Re: Need help creating a table
              Okechukwu Ossai

              You're welcome P R. I'm glad it helped.

               

              Ossai