Date, problem with set analysis

[carlos13]

Hello I have a problem with the following table:

I want to have this data table present in the analysis set.

Descripcion	Saldo	Periodo
Obligaciones  por bienes tomados en arrendamiento financiero Colones	724429,47	31/01/2011
Obligaciones  por bienes tomados en arrendamiento financiero Colones	11178557,93	31/01/2012

I work the analysisset with this formula: sum (  { < Periodo ={'<=AddMonths(Periodo,-12)'} >} Saldo)

The previous step I use is to make a comparison with  the select date  with last year data.

This is the data table thatshows as a result           

Tipo  Cuenta	Clasificación	31/01/2012	31/01/2011
PASIVO	Obligaciones  por  bienes tomados en arrendamiento financiero	₡11,178,557.93	0

The problem is that in the data table doesn’t show this : 724429.47 and must be  shown and instead is a 0.

[stevedark · Accepted solution]

Hi Carlos,

In your present expression the AddMonths is not being executed as a function, and is being taken as a literal string which does not compare to the date.

Something like this will probably give a better answer:

sum (  { < Periodo ={'<=$(=AddMonths(max(Periodo),-12))'} >}Saldo)

The $(= tells QlikView to evalulate the expression included in the brackets and return the result.  The max is required as the function will fail if more than one Period is returned.

Personally I would calculate an additional fields for year in the load script (using the Year function) and then do set analysis on that field.  Also, I would calculate the max year and the prior year in variables and use those.

I've uploaded a couple of Set Analysis examples to the community which may be useful to you.  One of these is here:

http://community.qlik.com/qlikviews/1175

Hope that helps,

- Steve

http://www.quickintelligence.co.uk/

[carlos13]

Thank you very much.