5 Replies Latest reply on Sep 26, 2018 10:45 AM by Jim Dehner

    Interpolation in Tableau

    Simmy Johney

      Hi everyone, I'm trying to calculate percentiles in tableau using the interpolation methodology. i'm not using percentiles from tableau as I'm working on survey data and my answer choices (1-5) are too limited to give me distinct data points. I have 20 different statements against which respondents have chosen from a scale of 1-5. The first step I need to do is calculate a cumulative distribution of the count of respondents from 1-5. It is going to look something like the table below. What is the calculation i should be using for every statement? For the next step of my methodology, I would be referencing the calculated %age and the corresponding scores. e.g. if I want to calculate 20th percentile, i would look up the two %ages in the table below between which 20% would fall. I would do this calculation later, I would first want to wrap my head around the calculation for the first step.

       

      ScoreCount of Respondents (Cumulative distribution)
      13%
      212%
      332%
      478%
      5100%
        • 1. Re: Interpolation in Tableau
          Jim Dehner

          Hi

          You are asking very specific questions - if you want very specific answers - please provide your TWBX workbook with the data included

           

          Jim

          • 2. Re: Interpolation in Tableau
            Simmy Johney

            Here is the twbx with the data. TIA!!

            • 3. Re: Interpolation in Tableau
              Jim Dehner

              see the attached

              I don't know where you are going with the analysis but this should give you a direction

               

              there is a table calculation that can be written as

               

              that returns this

               

               

              then you can calculate the running total of the values using

               

               

              and it will return this

               

               

              Jim

               

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              1 of 1 people found this helpful
              • 4. Re: Interpolation in Tableau
                Simmy Johney

                Thanks a ton, Jim. This is super helpful Any chance you could help me with the second step as well? with the same data, I now wish to see the running total of percentage for each answer code. I am wondering if I could make a table of sorts for referencing, like I would in Excel. I essentially want to check between which two cumulative distribution values would lets say "20%" fall and then what are the corresponding scores? I want to replicate this calculation in Tableau: = Closest lower score + (cut-off% – closest lower cumulative distribution %age) / (closest upper cumulative distribution %age - closest lower cumulative distribution %age). For instance, in this case, if my cut-off is 20%, then my calculation would be: 2 + (20%-12%) / (32%-12%). This is slightly complicated, any guidance for this?

                 

                Score

                Count of Respondents (Cumulative distribution)

                13%
                212%
                332%
                478%
                5100%
                • 5. Re: Interpolation in Tableau
                  Jim Dehner

                  see the attached

                   

                  this is a little complex because all the calculations are table calcs which are on the bottom of the order of operation

                  I added a parameter to select the target value

                  the calculation is

                   

                   

                  and it is computed across

                   

                  in the table it would look like this

                  of you can plot it to look like this

                  Jim

                  If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.