return the last value qliksense table

[Jsobrinho77]

Hi guys,

I need to show the last value for the column Value, based on Month column, how can I do this? Independent the table sort (asc or desc ...because I tried to use above and below and when I change the sort the values it's changed).

In this example, 01/02/2023 needs to show 9,80

[BrunPierre]

As below.

LOAD *,
If(Len(Trim(Value))=0, Peek(NewValue), Value) as NewValue
Inline [
Month,Value
01/01/2023,980
01/02/2023,
01/03/2023,1024
01/04/2023,1024
01/05/2023,1024
01/06/2023,1024 ];

Exit Script;

[Digvijay_Singh]

Please share expected output!

[Jsobrinho77]

first of all, thank you for your answer...

the expected result is, because this is the previous value, or if 01/01/2023 is null the expected value is 10,24 because is the next value. basically I need to show the close value.

[Digvijay_Singh]

Not sure of the expectations when multiple values are nulls  but aggr helps to sort values month wise and keep results consistent..

aggr(Coalesce(Value,above(Value),Below(Value),above(above(Value)), below(below(Value))),(Month,(NUMERIC,ASCENDING)))

I know it won't help completely but may be you get some new ideas to solve the problem 🙂

[BrunPierre]

---

As below.

LOAD *,
If(Len(Trim(Value))=0, Peek(NewValue), Value) as NewValue
Inline [
Month,Value
01/01/2023,980
01/02/2023,
01/03/2023,1024
01/04/2023,1024
01/05/2023,1024
01/06/2023,1024 ];

Exit Script;

[BrunPierre]

As below.

LOAD *,
If(Len(Trim(Value))=0, Peek(NewValue), Value) as NewValue
Inline [
Month,Value
01/01/2023,980
01/02/2023,
01/03/2023,1024
01/04/2023,1024
01/05/2023,1024
01/06/2023,1024 ];

Exit Script;