Transformar IF en SetAnalysis (sumar semana anterior)

[#1]

Necesito saber si es posible pasar a SetAnalysis la siguiente sentencia:

if(year(Fecha)=$(vAñoSemanaAnt),if(Semana=$(vSemanaAnt),sum(Cantidad),0))

Adjunto el documento con el que obtengo las sumas de las cantidades de la semana anterior a la actual (también tiene la opción de indicar más de una semana atrás).

Usando IF me funciona pero desearía usar SetAnalysis porque el documento es un ejemplo y creo que cuando el volumen de datos sea mayor el IF podría ser lento.

Saludos

[swuehl]

Hi,

if I understood you correctly (my spanish is pretty poor), try this as expression

=sum({<Fecha={"=year(Fecha)=$(vAñoSemanaAnt)"},Semana={'$(vSemanaAnt)'}>} Cantidad)

and see attached app.

Regards,

Stefan

[#3]

Thank you Stefan, your spanish is probably better than my english

I only have a bit problem with the variable vSemanaAnt. I mean, when i want to set the variable vSemanasAtras to a number (example 30 or bigger) then, vSemanaAnt is 0 or <0 and fails, so i need to control when it change of year. Do you know how to do it?

Regards,

thefourth

[swuehl · Accepted solution]

Hi thefourth,

I learned some spanish some years ago, but compared to your english... I would prefer to stay with english

If I understood your application correctly, you want to distinguish between the calendar year a date is in (say 2/1/2011 is in CY 2011)

and the year where the week of the date belongs to (e.g. for 2/1/2011 its still week 52/2010), right?

So, in my example, if vSemanaAtras=30, vSemanaAnt should be 52 and vAnoSemanaAnt should be 2010, right?

To get the year of the calendar week, you don't need complicated expressions to calculate, there is weekyear() function in QlikView.

Please see my attached modifications of variables and set expression.

Regards,

Stefan

[#5]

Really excellent Stefan! You understood everything in the right way. I did not know the weekyear() function.

Thank you very much,

thefourth