topic Re: Transformar IF en SetAnalysis (sumar semana anterior) in QlikView

Transformar IF en SetAnalysis (sumar semana anterior)

Sat, 30 Jul 2011 21:11:03 GMT

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

Re: Transformar IF en SetAnalysis (sumar semana anterior)

swuehl — Sat, 30 Jul 2011 22:52:30 GMT

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

Re: Transformar IF en SetAnalysis (sumar semana anterior)

Sun, 31 Jul 2011 08:33:14 GMT

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

Re: Transformar IF en SetAnalysis (sumar semana anterior)

swuehl — Sun, 31 Jul 2011 11:27:39 GMT

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

Re: Transformar IF en SetAnalysis (sumar semana anterior)

Sun, 31 Jul 2011 19:47:47 GMT

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

Thank you very much,

thefourth