Last Year Value in a Straight Table (Date as Dimension)

[lanlizgu]

Hi,

I would like to have a table with the date as dimension and in one of the expressions that I have, I would like to get the value of the previous year.

The thing is that I want to achive this set analysis without using above or other functions that could change in case of changing the order of the table.

Based on the following data sample I would like to have in a table the 20181231 as a dimension and the 972 value:

Test:
LOAD * INLINE [
Fecha, Value
20180101, 4897
20181231, 1289
20170101, 7892
20170608, 3648
20171231, 972
];

NoConcatenate
Test2:
LOAD
year(date(Date#(Fecha,'YYYYMMDD'),'DD/MM/YYYY')) as Year,
date(Date#(Fecha,'YYYYMMDD'),'DD/MM/YYYY') as FECHA,
Value
resident Test;

drop table Test;

Thank you.

[lanlizgu]

As I have many expressions, having a calculated dimension as follows is not a solution:

Dimension: Aggr(NODISTINCT addyears(Max(YearEnd(FECHA)),1),Year)

Expression: Sum(if(FECHA = Aggr(NODISTINCT Max(FECHA),Year),Value))