3 Replies Latest reply: Feb 13, 2014 3:00 AM by Marcus Sommer RSS

    Get a value for a specific row

    Oscar Marquez

      Hi,

       

           I hope you can help me with this, I've got a chart with month-year as dimension.

       

           We want to show average turn around time as expression, which is quite simple, but we want also to show TAT 90% fill rate, here's the sample table and the how fill rate is calculated.

       

      MonthTAT
      Nov-1320
      Nov-1315
      Nov-1323
      Nov-1354
      Nov-1315
      Nov-1311
      Nov-138
      Nov-132
      Nov-133
      Dec-135
      Dec-1312
      Dec-130
      Dec-131
      Dec-1320
      Dec-1319

       

      To calculate the fill rate, we sort TAT value A-Z by month, then count total of rows per month (nov=9, Dec=6) and calculate 90% out of that, and that's the row number used. So for Dec TAT 90% fill rate = 23 and Dec= 19. I could add a new column in my database with this value, but I was wondering if there's a way with rank + aggr function to get this.

       

      MonthTAT
      13-Nov2
      13-Nov3
      13-Nov8
      13-Nov11
      13-Nov15
      13-Nov15
      13-Nov20
      13-Nov23<- 90% item
      13-Nov54
      13-Dec0
      13-Dec1
      13-Dec5
      13-Dec12
      13-Dec19<- 90% item
      13-Dec20