pick the closest value to "x"

[andreip21]

Is there a possibility to pick the "right price" in Qlikview interface (not script)?

Here is my case:

SKU ID || Price || Stock Rotation (days)

1 || 100 || 40

1 || 80 || 12

1 || 50 || 5

2 || 900 || 90

2 || 850 || 45

2 || 750 || 22

3 || 550 || 20

3 || 400 || 10

3 || 350 || 5

I want a final table with only 1 price/ SKU considering that the stock rotation should be the closest value to 20 days :

1 || 80 || 12

2 || 750 || 22

3 || 550 || 20

The stock rotation is different based on selection, so this is the reason i want to calculate directly in the interface.

Thanks,

Andrei

[sunny_talwar · Accepted solution]

May be like this

Dimension

SKU ID

Expression

FirstSortedValue(Price, -fabs([Stock Rotation (days)] - 20))

FirstSortedValue([Stock Rotation (days)], -fabs([Stock Rotation (days)] - 20))

[andreip21]

is good only if Stock Rotation (days) is a dimension.

But i have to calculate it as one expression, it's difficult to have it also in the script.

some other possibilities?

[sunny_talwar]

Not really... what is your expression for Stock Rotation (days)?

[andreip21]

Hi Sunny, i did it with an AGGR expression

Thanks for help, i will mark your first reply with the correct answer.