Last value for record in table - QlikSnese

[RutiTaumanRubin]

Hi everyone,

I created table with the following fields: ID, Year , Grade

I want to create also calculate field named: 'Last Grade' that display the grade of last year to each ID:

ID	Year	Grade	Last Grade
11	2015	98
11	2016	25	98
11	2017	60	25
22	2015	40
22	2016	44	40
22	2017	54	44

Could you please advise?

Thank you..

[petter]

You don't mention whether you want this done in the load script in a data model table in the load phase or you want it done in a UI table in a sheet.

[petter]

If you need to do it in you load script this is a way of doing this:

SOURCE_DATA: 
LOAD * INLINE [
ID,Year,Grade,
11,2015,98,
11,2016,25
11,2017,60
22,2015,40
22,2016,44
22,2017,54
];

DATA:
LOAD
  ID,
  Year,
  Grade,
  If(Peek('ID')=ID,Peek('Grade'),'') AS [Last Grade]
RESIDENT
  SOURCE_DATA;

DROP TABLE SOURCE_DATA;

[petter · Accepted solution]

Or if you want to do it in the UI without preprocessing you table in the load script you can do it like this - with just the Above() function:

[RutiTaumanRubin]

Sorry I forgot to mention - in the UI level ..

It is working - Thank you!!!

[petter]

You're welcome - happy to help

[RutiTaumanRubin]

Hi,

Another question in this subject..

I need to display by default only the records of max year(unless the user select another selection)

When I filter on field "ID" (if year=max(all {$} year),ID) and unmark "Include null values".. only the records of 2017 appear but the calculate field "Last Grade" not getting values (all the records appear as null) .

Any Idea?

Thank you!

[petter]

Well you already filtered to get only 2017 and the Above function will look "above" and doesn't find any value above....

[petter]

I would not have a filter on ID but rather on the measurements by using set expressions:

One dimension which is ID

Two measures:

Sum( {1<Year={$(=Max({1} Year))}>} Grade)   ,    label:   =Max({1} Year)

Sum( {1<Year={$(=Max({1} Year)-1)}>} Grade),    label:  =Max({1} Year-1)