Negavtive Rank in the FirstSortedValue

[#1]

I am trying to get the next and the previous value to the firstsortedvalue.

In the below expression, can I give a -ve number (<-1) to the rank to get the next value. If not, how do I get the next value. I can not have it in the script as the value is dynamic.

FirstSortedValue([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] value, sort_weight [,rank])

TIA.

Srikanth

[swuehl]

You can use 2 as rank to get the second lowest value sorted by sort_weight (Ascending).

FirstSortedValue([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] value, sort_weight , 2)

If you want the highest / second highest value, use a negative sort weight (to change to descending sort order):

FirstSortedValue([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] value, -sort_weight)

FirstSortedValue([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] value, -sort_weight,2)

[#3]

in the below table, my firstsortedvalue is ID=  108026251. Now I want to print the previous ID (107038165) and next ID (109116074)   on the report. I am able to get the previous value by using a rank 2, How do I get next value. (Firstsortedvalue could different based on the filters (ex:date, code)).

GRPID	ID	Date	Code
80092427	90575212	27-Jul-12	BBB+
80092427	94315250	18-Jul-13	BBB
80092427	98775480	7-Apr-14	BBB
80092427	107038165	2-Apr-15	BBB
80092427	108026251	18-Aug-15	RAS
80092427	109116074	25-Mar-16	BBB

Thanks

[swuehl]

Your firstsortedvalue is ID = 108026251? Why is that? You said you are using filters to find the firstsortedvalue, but with the same filters, I doubt that "next" and "previous" values are (109116074)  and (107038165) .

If possible, upload a small sample QVW,please.

[#5]

Sorry for the confusion. I meant, IF I my firstsortedvalue is 108026251, then the I can calculate the previous value by giving rank=2. In the same way, is there a way to calculate the next value (which is 107038165 in this example.)

Thanks

Srikanth

[#6]

Please see the attached example.

Thanks.

[swuehl]

You already got the last / highest date in the date range you've set using set analysis.

Do you want to remove the set date range and finde the next higher date value outside the set date range?

[#8]

Yes thats right.

[Gysbert_Wassenaar · Accepted solution]

NextIncidentdate:

=Date(FirstSortedValue({<[Incident Date Value] = {"$(='>' & Date(Min({AS}[Incident Date Value])))"}, [Incident Date]>}[Incident Date Value], [Incident Date]))

PreviousIncidentdate:

=Date(FirstSortedValue({<[Incident Date Value] = {"$(='<' & Date(Max({AS}[Incident Date Value])))"}, [Incident Date]>}[Incident Date Value], -[Incident Date],2))

NextIncident:

FirstSortedValue({<[Incident Date Value] = {"$(='>' & Date(Min({AS}[Incident Date Value])))"}, [Incident Date]>}[Incident ID], [Incident Date])

PreviousIncident:

FirstSortedValue({<[Incident Date Value] = {"$(='<' & Date(Max({AS}[Incident Date Value])))"}, [Incident Date]>}[Incident ID], -[Incident Date],2)

[swuehl]

Yes, Gysbert already wrote it down what I was thinking about.

Maybe use a larger equal so you don't miss an incident:

NextIncidentdate:

=Date(FirstSortedValue({<[Incident Date Value] = {"$(='>=' & Date(Min({AS}[Incident Date Value])))"}, [Incident Date]>}[Incident Date Value], [Incident Date]))

or

=Min(

{<[Incident Date Value] = {"$(='>=' & Date(Min({AS}[Incident Date Value])))"}, [Incident Date]>} [Incident Date Value])

NextIncident:

FirstSortedValue({<[Incident Date Value] = {"$(='>=' & Date(Min({AS}[Incident Date Value])))"}, [Incident Date]>}[Incident ID], [Incident Date])