topic Re: How to use FirstSortedRow and Above/Below in a table as expression? in QlikView

How to use FirstSortedRow and Above/Below in a table as expression?

mrichman — Wed, 25 Nov 2020 16:16:04 GMT

Hi Community!

I'm currently trying to use the FirstSortedRow and Above/Below functions in a combination.

My data mainly contains of Client,Date & Stage. I would like to add a column which calculates the status per max date and group it by client. I have done in in the back-end (Script) with the help of this thread (https://community.qlik.com/t5/QlikView-App-Development/Get-Max-Date-and-Max-Value-of-a-ID/td-p/1095223) and it works well, however the requirement now in using it as expression.

I calculated max_date with: =Max(Total<Client>Date)
I calculated max_datestatus: =if(Date=Max(TOTAL <Client>Date),Only(Stage))

Current Result:

Ideally:

Thanks in advance.

Re: How to use FirstSortedRow and Above/Below in a table as expression?

sagarjagga — Fri, 21 Feb 2020 13:55:38 GMT

Hi Mrichman,

You can use below set analysis as measure.

Aggr(Nodistinct FirstSortedValue(Stage,-Date),Client)

Hope this will help!!!

Re: How to use FirstSortedRow and Above/Below in a table as expression?

sunny_talwar — Fri, 21 Feb 2020 14:02:32 GMT

or even this

FirstSortedValue(TOTAL <Client> Stage,-Date)

Re: How to use FirstSortedRow and Above/Below in a table as expression?

Brett_Bleess — Wed, 11 Mar 2020 19:05:31 GMT

Did either of the two posts get you what you needed? If so, do not forget to close out your thread by returning and on the post that helped, use the Accept as Solution button to mark that post as the correct answer to give the poster credit and let other Members know that worked for you. If you are still working on things, please leave an update, and if you did something different, please consider posting what that was and once posted, use the button on that post to close the thread.

Regards,
Brett