Set Analysis compare with Previous Month

[rfigueroa]

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.

[Miguel_Angel_Baeyens]

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

[rfigueroa]

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)"