3 Replies Latest reply on Oct 11, 2018 8:08 AM by Daniel Jun

    How does Tableau calculate window_percentile?

    Daniel Jun

      How does Tableau calculate the WINDOW_PERCENTILE function? What equation / process does it use?

       

      Attached is my workbook using the Superstore data. Using the Quantity column, I'm interested in determining the Quantity value that is the 25th percentile based on the current row and previous 3 rows. The calculated field (called "Calc Quant Wind P") is: WINDOW_PERCENTILE(SUM([Quantity]), 0.25, -3, 0)

       

      For example, in the fourth row of the screenshot below (row with yellow highlight) the Quantity is 1 and the previous three Quantity are 3, 9, and 6. Tableau says that the 25th percentile for the four Quantity values (3, 9, 6, 1) is 2.50. How is the 2.50 calculated? What equation / process is used?

       

       

      Thanks in advance,

      Dan

        • 1. Re: How does Tableau calculate window_percentile?
          Jonathan Drummey

          Hi,

           

          As of roughly Tableau v9.2 all the percentile calculations in Tableau – PERCENTILE(), WINDOW_PERCENTILE(), RANK_PERCENTILE(), and percentile reference distributions – use the R-7 algorithm also used by MS Excel Quantile - Wikipedia, here's how it works with the 4 rows in your example:

           

          Value = x

          Sorted the values x1, x2, x3, etc. are 1, 3, 6, 9

          number of values N = 4

          percentile p = .25

           

          index h = (N-1)*p + 1 = (4-1) * .25 + 1 = 1.75

           

          If h was an integer then having ordered the values as 1st, 2nd, 3rd, 4th then the percentile result would be the hth value. However since h is a decimal this method uses a linear interpolation formula between x values that are the the floor(h)'th value and the ceiling(h)'th value. The way I think of this is that we're going to travel h% along the number line between those two x values.

           

          In this example, h is 1.75 so the 25th percentile will be linearly interpolated between the 1st value (1) and 2nd value (3), and since the decimal part is .75 we're going 75% of the way from the 1 to 3 and the "distance" is (3-1) * .75 = 1.5, then finally the 25th percentile is 1 (the 1st value) + 1.5 = 2.5.

           

          More generally put the R-7 formula for percentile result Q = (x for the integer hth value) + (decimal portion of hth value) * ((x for the (integer hth value + 1) - (x for the integer hth value)).

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: How does Tableau calculate window_percentile?
            seraj alam

            WINDOW_PERCENTILE(expression, number, [start, end])

            Returns the value corresponding to the specified percentile within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.

            Example

            WINDOW_PERCENTILE(SUM([Profit]), 0.75, -2, 0)) returns the 75th percentile for SUM(Profit) from the two previous rows to the current row.

             

            If that doesn't help, if you can post an example TWBX file, I'll take a look.

            • 3. Re: How does Tableau calculate window_percentile?
              Daniel Jun

              Thank you for the quick and thorough response!