topic Challenging problem : Error between years in variables. in QlikView

Challenging problem : Error between years in variables.

marcel_olmo — Thu, 02 Dec 2010 18:08:12 GMT

Hi guys, I got stuck the whole day doing this stuff.

I have two variables in my document : maxYear and preMaxYear.

1-MaxYear : If Year is not clicked, it will get the maximum year possible, otherwise, it will get the selected year.

MaxYear = IF(getpossiblecount([Año]) = 1 ,Año,max(Año))

2-preMaxYear : If Year is not clicked, it will get the second maximum year possible, otherwise, it will get the selected year -1.

preMaxYear =IF(getpossiblecount([Año]) = 1 ,Año - 1,max(Año,2) )

I'm doing a comparison between profit and loss between maxYear and preMaxYear.

If there is no year clicked, and maxYear = 2010 (by default) and preMaxYear = 2009 (by default as well). Everything is fine :

Otherwise, if I select Year = 2010, the same should happen, because maxYear and preMaxYear take the same values, but it doesn't work fine for the preMaxYear Table, as you can see :

I'm trying to fix that, I've thought about duplicating the field year (I thought it was some associating problem), but it doesn't work.

I have a master calendar. I've changed into a calendar inside the facts table. But it doesn't work as well.

I have no idea how to solve this problem.

Any kind of help would be appreciated.

Many thanks in advance!!!

Challenging problem : Error between years in variables.

Fri, 03 Dec 2010 07:33:13 GMT

Hi,

Try sum( $ {1 <Ano= { $(maxYear) } >} Profit ) instead of sum( $ { <Ano= { $(maxYear) } >} Profit ) . That's because the year selection might restrict values in other columns.

-Alex

Challenging problem : Error between years in variables.

marcel_olmo — Fri, 03 Dec 2010 13:49:12 GMT

Thanks Alexander for your Reply. I'm afraid is more complicated than that. I've tried ( in one of my many attempts to make it work and it doesn't work fine).

But recently I've discovered that only the account which doesn't have "facts" in correlative years (2010,2009,2008 and so on), are the one which fails.

What I mean is, if my current selected year is 2010, and I have an account which belongs only to 2009, it puts 0 in the partial result. But no in the whole result.

I don't understand anything!!! 😞

This is how I currently calculate my profit and loss statements :

Profit (The accounts which begin with number 7, Manager1 is the result row,and the previous last year):

sum( {1<[Año] = {$(preMaxYear)}, Manager1 = {"=LEFT(Manager1,5) = 'D.TOT' "}, N_Fila_Cuenta = {"=LEFT(N_Fila_Cuenta,1) = '7' "} >} [Asiento Importe])

Loss (The accounts which begin with number 6, Manager1 is the result row,and the previous last year):

sum( {1<[Año] = {$(preMaxYear)}, Manager1 = {"=LEFT(Manager1,5) = 'D.TOT' "}, N_Fila_Cuenta = {"=LEFT(N_Fila_Cuenta,1) = '6' "} >} [Asiento Importe])

And my Profit and Loss calculation is the sumation of the two formulas above (it doesn't work fine for non correlative accounts:

column(1) + column(2).

And this Profit and Loss formula works fine in every case, and I don't know why :

sum({$<[Año] = {$(preMaxYear)}, Manager1 = {"=LEFT(Manager1,5) = 'D.TOT' "} >} [Asiento Importe])

I know maybe is some kind of association error, but I cannot look at the original code of Qlikview, so I cannot debug or test it very well.

Any suggestion??

Many thanks in advance!!