Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

lanlizgu
Contributor II

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

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.

 

Labels (4)
1 Reply
lanlizgu
Contributor II

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

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