topic Re: Get a value for a specific row in QlikView

Get a value for a specific row

Anonymous — Fri, 24 Jan 2014 00:05:29 GMT

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

Re: Get a value for a specific row

marcus_sommer — Fri, 24 Jan 2014 12:52:27 GMT

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

Re: Get a value for a specific row

Anonymous — Wed, 12 Feb 2014 16:53:36 GMT

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

marcus_sommer — Thu, 13 Feb 2014 08:00:14 GMT

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

- Marcus