Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

rfigueroa
New Contributor III

Set Analysis compare with Previous Month

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.

2 Replies
MVP
MVP

Re: Set Analysis compare with Previous Month

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
New Contributor III

Re: Set Analysis compare with Previous Month

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

qv1.JPG

Community Browser