2 Replies Latest reply: Oct 31, 2011 1:32 PM by Roberto Figueroa RSS

    Set Analysis compare with Previous Month

    Roberto Figueroa

      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.


        • Re: Set Analysis compare with Previous Month
          Miguel Angel Baeyens de Arce



          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
              Roberto Figueroa

              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:







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