topic Date, problem with set analysis in QlikView

Date, problem with set analysis

carlos13 — Tue, 10 Apr 2012 21:45:22 GMT

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.

Date, problem with set analysis

stevedark — Tue, 10 Apr 2012 22:10:30 GMT

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/

Date, problem with set analysis

carlos13 — Tue, 10 Apr 2012 22:56:16 GMT

Thank you very much.