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

# Interpolation in Tableau

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

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

Here is the twbx with the data. TIA!!

• ###### 3. Re: Interpolation in Tableau

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

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

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.