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

# Get a value for a specific row

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.

 Month TAT 13-Nov 2 13-Nov 3 13-Nov 8 13-Nov 11 13-Nov 15 13-Nov 15 13-Nov 20 13-Nov 23 <- 90% item 13-Nov 54 13-Dec 0 13-Dec 1 13-Dec 5 13-Dec 12 13-Dec 19 <- 90% item 13-Dec 20
• ###### Re: Get a value for a specific row

1. an accumulation for your value

rangesum(above(total sum(TAT), 0, rowno(total)))

2. total value for TAT in Month

sum(total <Month> TAT)

3. check if 1/2 < your condition

if(rangesum(above(total sum(TAT), 0, rowno(total))) / total value for TAT in Month < 0.9, 'x', 'y')

- Marcus

• ###### Re: Get a value for a specific row

Thanks Marcus but that approach didn't work, as TAT values can fluctuate a lot, 90% of accumulation / total sum(TAT)  is not the same as taking the row where 90% of the items are.

• ###### Re: Get a value for a specific row

Perhaps you need further conditions in the expressions. To compare your conditions against a variable could be bring more flexibility.

- Marcus