# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## How would you show the last value possible?

Hi All,

- Qlik Sense -

How would you solve this in an expression? I have 2 columns both year and value. Not all years have a value.  If I select a year without value, I would like to show then the last possible value available.

Example. if I select 2022, then it would show 2134. This is the last possible value available. Same for 2021. However, when I select 2019, then it would show the correct value which is 321.

Year, Value

2018, 1000

2019, 321

2020, 2134

2021,

2021

2022

];

1 Solution

Accepted Solutions
Honored Contributor

## Re: How would you show the last value possible?

ABC:

[

Year, Value

2018, 1000

2019, 321

2020, 2134

2021,

2021

2022

];

In chart, Define

Year as Dim,

If(Len(Trim(Value))=0, above(Value),Value) as Expression..

now check the values.

7 Replies
MVP

## Re: How would you show the last value possible?

Not ideal, but see if this can work

Honored Contributor

## Re: How would you show the last value possible?

ABC:

Year,

If(Len(trim(Value)) = 0, peek(Value),Value) as Value;

[

Year, Value

2018, 1000

2019, 321

2020, 2134

2021,

2021

2022

];

New Contributor III

## Re: How would you show the last value possible?

Not ideal indeed. You have any other idea?

New Contributor III

## Re: How would you show the last value possible?

How would you do it as an expression? Also the same?

MVP

## Re: How would you show the last value possible?

Front end? no... but I guess you were okay doing this on the back end...

Honored Contributor

## Re: How would you show the last value possible?

ABC:

[

Year, Value

2018, 1000

2019, 321

2020, 2134

2021,

2021

2022

];

In chart, Define

Year as Dim,

If(Len(Trim(Value))=0, above(Value),Value) as Expression..

now check the values.

New Contributor III

## Re: How would you show the last value possible?

I was trying to get the set analysis to work - but couldn't get the last part:

// this gives you the max year (assuming it was in order, if not disregard

Max({<Value -= {''}>}  Year)

// gives you all years that do not have a null number

At that point, you can apply set analysis again to have the Year = this part

e.g.

sum({<Year={"2020"}>} Value)

would yield 2134, etc.

However, when I applied the second one

sum({<Year={"=Max({<Value -= {''}>}  Year)"}>} Value)

It kept summing all the non nulls.

(I forget the eval order)

Sorry it is not the full answer, but hopefully it puts you on the right path.

Also looked at using the P() function in set analysis as well.

Good luck).