Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Month | TAT |
---|---|
Nov-13 | 20 |
Nov-13 | 15 |
Nov-13 | 23 |
Nov-13 | 54 |
Nov-13 | 15 |
Nov-13 | 11 |
Nov-13 | 8 |
Nov-13 | 2 |
Nov-13 | 3 |
Dec-13 | 5 |
Dec-13 | 12 |
Dec-13 | 0 |
Dec-13 | 1 |
Dec-13 | 20 |
Dec-13 | 19 |
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 |
I think you need 3 steps for your task:
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
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.
Perhaps you need further conditions in the expressions. To compare your conditions against a variable could be bring more flexibility.
- Marcus