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

[mrichman]

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/10952...) 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. 

[sagarjagga]

Hi Mrichman,

You can use below set analysis as measure.

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

Hope this will help!!!

[sunny_talwar · Accepted solution]

or even this

FirstSortedValue(TOTAL <Client> Stage,-Date)

[Brett_Bleess]

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