topic Re: Set Analysis compare with Previous Month in QlikView

Set Analysis compare with Previous Month

rfigueroa — Fri, 28 Oct 2011 23:42:08 GMT

Hi all..

I'm trying to make a chart plotting the relation of current month vs. previous month, within current year.

I've tried some set analysis examples found on this forum but can't get it work.

I attached an example of what Im trying to do.

Thanks in advance.

Roberto.

Re: Set Analysis compare with Previous Month

Miguel_Angel_Baeyens — Sat, 29 Oct 2011 00:51:40 GMT

Roberto,

Deberías revisar tu tabla de calendario para verificar que los formatos de los distintos campos son los que realmente necesitas. En tu caso, la expresión que buscas es

Avg({< dt = {">=$(=Date(MonthStart(Today()), 'DD/MM/YYYY hh:mm:ss TT'))<=$(=Date(MonthEnd(Today()), 'DD/MM/YYYY hh:mm:ss TT'))", ">=$(=Date(MonthStart(AddMonths(Today(), -1)), 'DD/MM/YYYY hh:mm:ss TT'))<=$(=Date(MonthEnd(AddMonths(Today(), -1)), 'DD/MM/YYYY hh:mm:ss TT'))"} >} PrecioPago)

Se deben utilizar tantas funciones de formato por la diferencia entre lo que devuelve la función Date() y el formato del campo "dt". Podrías crear también un campo "flag" con valor 1 si la fecha del calendario es del mes actual y otra para el mes anterior.

Espero que te sirva.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Set Analysis compare with Previous Month

rfigueroa — Mon, 31 Oct 2011 17:32:45 GMT

Thank you Miguel, I'm going to answer in english because I've seen other people complaining about writing in other languages.

In the example that I upload before, the Calendar data is loaded from a DB:

Calendar:

LOAD

SQL SELECT dt, Y as Year, (Y*100)+M as MesAnio

FROM Calendar

WHERE Y >= 2009 and Y <= $(vCurrentYear);

In this case, fields Y and M are both integer, so obviously MesAnio field are integer also, besides, in the preview of the Calendar table I see a number right aligned so I though it's a number on the QV table, is there a way to confirm that I'm using a number?

If I select one month only, the expression is evaluated correctly... as you can see on the picture below the column title states "AVG({$<MesAnio={201107}>} PrecioPago)"