To get the nth ranked value for a dimension at script level

[prachisangewar1]

Hi,

I have a requirement where i need to get the nth ranked value for a dimension, sample table given below

The Column Threshold value is what I want to compute.

So we are looking at a range of dates for the customers and the indicator is computed on some logic.

The Threshold value is based on the value in the indicator column. It is the nth rank value for each customer where n is the value in the indicator column.

I have tried to use Firstsortedvalue and Max , but both need a number or a variable in the rank agreement. I want to use the indicator column.

Formula i used :

FirstSortedValue(Value,-Value,indicator) - gives wrong result

Max(Value,indicator) -  gives wrong result

I cannot give a fixed number in the last agreement of both the functions as it changes with customer. Please advise if any solution for this scenario.

Date	Customer	Value	indicator	Threshold value
1/1/2019	a	24	4	20
2/1/2019	a	56	4	20
3/1/2019	a	-75	4	20
4/1/2019	a	12	4	20
5/1/2019	a	45	4	20
6/1/2019	a	-10	4	20
7/1/2019	a	-5	4	20
8/1/2019	a	20	4	20
1/1/2019	b	634	5	43
2/1/2019	b	43	5	43
3/1/2019	b	454	5	43
4/1/2019	b	34	5	43
5/1/2019	b	463	5	43
6/1/2019	b	646	5	43
7/1/2019	b	43	5	43
8/1/2019	b	33	5	43

[anushree1]

Could you please state the input and the output in this scenario as the explanation seems a little confusing

[prachisangewar1]

Hi,

Date, Customer, Value and indicator are input.
The out put required is Threshold value.
I have to lookup on the customer and get the nth highest value in the "Value" column, where n is the value in indicator column.
Let me know if any other clarifications required.

[anushree1]

Please use the below script:

Table:

LOAD
"Date ",
"Customer",
"Value",

indicator
FROM [lib://test apps/nth threshold.xlsx]
(ooxml, embedded labels, table is Sheet1)
;

Final:

Load * ,
AutoNumber(RowNo(),"Customer") as Seq
Resident Table
Order by "Customer",Value desc ;
drop table Table;
Left Join
Test:
Load "Customer",
"Value" as Threshold
resident Final
Where Seq=indicator;

In the front end create a Straight Table with Dimension Date, Customer and Expression Only(Threshold)