1 Reply Latest reply on Aug 31, 2018 2:16 PM by Patrick A Van Der Hyde

    95th percentile based on Workweek

    Sean Kern

      Working on creating 95% percentile value/average/count calculations for my TeraData DB table showing service requests. Have a column showing Workweek Completed beginning with year(2018-15, 2018-32, etc...) and one with Hours at Step. I've set this up in the past on a pivot chart in excel with a formula similar to IF(A1<PERCENTILE($A$1:$A$20,0.95),K1,""). A1-A20 is the first Workweek, and then would have to populate the formula for the next Workweek added(A21-A46 for example), with the updated formula for that group.

       

      I'm working on creating this calculation in Tableau to automate all of this. Need percentile values for each row with the same Workweek. So need them grouped somehow, and if the value of the rows Hours at Step is less than the percentile calculation of the workweek group, then the 95th% value for the row is the Hours at Step. Then if that value exists for that row, then it's counted for the 95th% Count calculation.

       

      I have no idea how to start this. If I wanted these values for my entire set of data, I'm on the trolley, but have no idea how to determine separate percentiles for any row with a certain workweek code and then create another column of values based on if its less than the percentile of that group.

       

      Sorry for the lengthy and invariably confusing explanation but need some help. Will offer first born child.