topic A field equal to another field? (Avoiding GetFieldSelections) Challenging! in QlikView

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

marcel_olmo — Mon, 14 Mar 2011 18:31:33 GMT

Hey guys!!

Here's my situation :

1- I have two calendar Table types : Regular Calendar Table and Rolling Months Calendar Table. (To calculate earnings of the last 12 months).

And I have to mix them to get my desired result in a table.

I mean, if I want to know which is the last sales month I need to click on AsOfYear = '2010' ( Year field of the Rolling Months Table) and Year ='2010' (Year Field of Regular Calendar Table).

I get that the last month is September, as you can see :

If I don't select anything, my result is :

And my desired result would be :

My point is that I don't know how to make AsOfYear = Year in set analysis avoiding to use getfieldselections.

By now I'm using this expression :

='max({$<Year={$(=if(getfieldselections(AsOfYear),getfieldselections(AsOfYear),'*'))} >} total <Year> Month)'

It means, if I have AsOfYear Selected, I get the maximum month of this year, and If I don't have any AsOfYear selected, I get the last month of all the years (so, it means 12). And I would need to get the12th month for every year except the last one (which I need to be the 9th month).

I hope my explanation was understandable.

Many thanks in advance!!!!!

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

Anonymous — Mon, 14 Mar 2011 19:42:59 GMT

if create an variable , exemple : 'variable_year' , with value the field , 'Year'. You can use the variable in field, AsOfYear , in ' set analysis'... ( or the opposite )

would be something like:

max({$<AsOfYear={$(variable_year)}>}Month)

solves 'AsOfYear = Year ' ?

sorry my english, I'm from Brazil.

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

marcel_olmo — Tue, 15 Mar 2011 09:36:25 GMT

Thanks Rodrigo for your answer,

Here you have a Table Box with the two fields : AsOfYear (of Rolling Calendar Table) and Year (Regular Calendar Table).

I've tried your expression : max({$<Year={$(variable_year)}>}Month). Where $(vAsOfYear) is equal to the field AsOfYear

And I found no results.

Any idea how can I achieve my desired result ?

Many thanks in advance!!

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

Miguel_Angel_Baeyens — Tue, 15 Mar 2011 10:32:20 GMT

Hello Marcel,

You may try

Max({< Year = P(AsOfYear)>} Month)

P() returns all possible values (sensitive to the selections done) for year AsOfYear. It can use set analysis.

Hope that helps.

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

marcel_olmo — Tue, 15 Mar 2011 11:11:18 GMT

Many thanks Miguel Angel for your quick answer,

As you can see in my first post (which explains my whole problem).

I have closed years (2005,2006,2007,2008,2009) with the maximum month = 12.

And I have an opened year (2010), with the maximum month = 9.

If I use you expression (selecting AsOfYear = 2010), I can get the maximum month = 9 (which is correct), but I need to calculate over all the years.

This is the result with your expression :

And this ismy desired result :

Many thanks in advance!!

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

Miguel_Angel_Baeyens — Tue, 15 Mar 2011 11:49:57 GMT

Hello Marcel,

I'd create a new dimension that has bot year and month for every date, and use MaxString() instead of Max()

Hope that helps.

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

marcel_olmo — Tue, 15 Mar 2011 23:03:33 GMT

THanks again for worrying about my issues Miguel Angel,

I have to beg your perdon because I can't understand why I have to do that.

My problem is that I need to "force" Year = AsOfYear always to get my desired result "as I understand".

If I use your function is useful for the year selected, but not for all the years. I need to calculate this without using functions which means select Calendar stuff.

I see you're from Zaragoza, so I'd prefer to explain myself in our language to make me understand better :

Hola Miguel Angel de nuevo.

El tema es que tengo que mezclar los dos tipos de calendario para calcular ciertos ratios. El calendario con Años y Meses naturales, y el calendario con Años y meses "de arrastre", es decir, unas ventas de julio 2010, se calcularian desde septiembre 2009 hasta julio 2010. El concepto esta bien explicado en el foro como "Rolling Months".

Por set analysis, si clico el año que quiero, es facil igualar los dos años, para obtener su maximo mes. EL problema es controlar cuando "no hay ningun año seleccionado". Hasta ahora tenia que si no seleccionabas ningun año, el maximo mes era igual a 12. Pero esto es incorrecto, ya que en mi caso el año 2010 no lo tengo cerrado, y deberia ser igual a 9.

Es un gran quebradero de cabeza que no se me ocurre como solucionar.

Muchisimas gracias de antemano por tu ayuda desinteresada, y si algun dia requieres de la mia, no dudes en pedirmela.

Saludos!!!

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

marcel_olmo — Wed, 16 Mar 2011 16:47:12 GMT

I've found the solution!!! Thanks anyway!!!

My solution was to recalculate (on the loading script) the maximum value of the month grouping by year and company in a resident table.

My problem is I've to reload all the data (thousands of rows), but it was worth it.

Best regards!!!

A field equal to another field? (Avoiding GetFieldSelections) Challenging!

marcel_olmo — Fri, 01 Apr 2011 15:16:48 GMT

Hey Miguel thanks again for your useful help.

Finally I got a fully desirable solution, here's what I've done :

If you want to compare one field to another field is completely impossible doing set analysis, so what I've done is an auxiliar table that matches the two fields I want to compare doing joins between the two tables.

For example :

Field1, Field2, IsMatched

'Hello', 'Hello', 1

'Hello', 'Bye', 0

I know is a quite stupid thing, but at the end is very useful.

Thanks again Miguel! Cheers!!