Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
Not ideal, but see if this can work
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
];
Not ideal indeed. You have any other idea?
How would you do it as an expression? Also the same?
Front end? no... but I guess you were okay doing this on the back end...
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.
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).