Last value

[RutiTaumanRubin]

Hi everyone,

I have table with the following fields: ID, P_Year , P_Status

I want to create also calculate field named: 'P_Last_Status' that display the status of last year for each ID:

ID	P_Year	P_Status	P_Last_Status
11	2015	Success
11	2016	Failure	Success
11	2017	Good	Failure
22	2015	Success
22	2016	Good	Success
22	2017	Success	Good

I also want to display by default only the record of max year (in this example only the records of 2017) unless the user choose value in field P_Year then the table will filter according to the selection..

I try to calculate the field P_Last_Status with function above() but it's not working because the filer..

Could you please advise how can I do it?

Thank you..

[OmarBenSalem]

order your table by ID and P_YEAR than use peek() or previous()

Please refer to:

Peek() vs Previous() – When to Use Each

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/InterRecordFun...

QlikView Peek Function

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/InterRecordFun...

[RutiTaumanRubin]

Hi,

Thank you for your response..

The problem is that I need to add filter to the table, Year = Max(Year) , so only the records of 2017 will remain:

ID	P_Year	P_Status	P_Last_Status
11	2017	Good	Failure
22	2017	Success	Good

In your solution when I added the filter I got no value in field P_Last_Status..

Could you please advise?

Thank's

[bramkn]

If P_Status is a field. add another field with the last status in the dataset.

[menta]

use peek or previous or above

if(ID=peek(ID),Peek(P_Status),'')