Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mrichman
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.

LOAD * INLINE [

    Year, Value

    2018, 1000

    2019, 321

    2020, 2134

    2021,

    2021

    2022

];

Thanks in advance!

1 Solution

Accepted Solutions
balabhaskarqlik
Honored Contributor

Re: How would you show the last value possible?

Load below table, then

ABC:

LOAD * INLINE

[

    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
MVP

Re: How would you show the last value possible?

Not ideal, but see if this can work

Capture.PNG

balabhaskarqlik
Honored Contributor

Re: How would you show the last value possible?

ABC:

Load

    Year,

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

LOAD * INLINE

[

    Year, Value

    2018, 1000

    2019, 321

    2020, 2134

    2021,

    2021

    2022

];

mrichman
New Contributor III

Re: How would you show the last value possible?

Not ideal indeed. You have any other idea?

mrichman
New Contributor III

Re: How would you show the last value possible?

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

MVP
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...

balabhaskarqlik
Honored Contributor

Re: How would you show the last value possible?

Load below table, then

ABC:

LOAD * INLINE

[

    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.

rscampbell
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).

Community Browser