Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
Thank you very much.