Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mrichman
Creator II
Creator II

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

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.

View solution in original post

7 Replies
sunny_talwar

Not ideal, but see if this can work

Capture.PNG

balabhaskarqlik

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
Creator II
Creator II
Author

Not ideal indeed. You have any other idea?

mrichman
Creator II
Creator II
Author

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

sunny_talwar

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

balabhaskarqlik

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.

Anonymous
Not applicable

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