topic Re: Negavtive Rank in the FirstSortedValue in QlikView

Negavtive Rank in the FirstSortedValue

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Negavtive Rank in the FirstSortedValue

swuehl — Fri, 15 Apr 2016 14:13:23 GMT

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)

Re: Negavtive Rank in the FirstSortedValue

Fri, 15 Apr 2016 14:20:36 GMT

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

Re: Negavtive Rank in the FirstSortedValue

swuehl — Fri, 15 Apr 2016 14:30:44 GMT

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.

Re: Negavtive Rank in the FirstSortedValue

Fri, 15 Apr 2016 14:37:57 GMT

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

Re: Negavtive Rank in the FirstSortedValue

Fri, 15 Apr 2016 14:43:10 GMT

Please see the attached example.

Thanks.

Re: Negavtive Rank in the FirstSortedValue

swuehl — Fri, 15 Apr 2016 14:56:31 GMT

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?

Re: Negavtive Rank in the FirstSortedValue

Fri, 15 Apr 2016 15:00:54 GMT

Yes thats right.

Re: Negavtive Rank in the FirstSortedValue

Gysbert_Wassenaar — Fri, 15 Apr 2016 15:02:59 GMT

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)

Re: Negavtive Rank in the FirstSortedValue

swuehl — Fri, 15 Apr 2016 15:08:24 GMT

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]))

=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])

Re: Negavtive Rank in the FirstSortedValue

Fri, 15 Apr 2016 15:32:10 GMT

Thanks so much for the quick solution.

Re: Negavtive Rank in the FirstSortedValue

Fri, 15 Apr 2016 15:32:26 GMT

Thanks a lot.