2 Replies Latest reply: Apr 30, 2015 10:09 AM by Arun Aggarwal RSS

    Set Analysis Comparison with Variables

    Arun Aggarwal

      Hi everyone,

       

      I am attempting to use the following expression in a measure:

       

      sum({<Month = {'$(vCurrentMonth)'}>}IntakeBU)

      -

      sum({<Month = {'>= $(vCurrentMonth)'}>}IntakeValue)


      The first set statement before the minus sign works fine. The one after it always outputs to zero.


      The value of vCurrent month is month(today()).


      I have verified that with a sum(IntakeValue) is far greater than 0 with the appropriate months selected. Can anyone explain why this doe not work or recommend a work-around that does not involve hard-coding the values?


      Thanks

        • Re: Set Analysis Comparison with Variables
          Gysbert Wassenaar

          Try this:

           

          change vCurrentMonth to =num(month(today()))

           

          change your expression to:

          sum({<Month = {'>=$(vCurrentMonth)<=$(vCurrentMonth)'}>}IntakeBU)

          -

          sum({<Month = {'>= $(vCurrentMonth)'}>}IntakeValue)


          The cause is that $(vCurrentMonth) returns a string. The result is that you compare a numeric month value (because of the >= operator) with a string value. By changing the variable so that it return a number it's possible to use the >= and <= operators. To make the first part of the chart expression work I changed the modifier so it works with the number value of the variable.