3 Replies Latest reply: Jul 11, 2011 10:47 AM by vishalj88 RSS

    Format question

      I have two variables vPeriod and vPeriod_Test

       

      vPeriod = '07/05/2011'  (This can be any random date; I'm just taking July 5th as an example)

       

      I'm trying to replicate the same format in vPeriod_Test using a formula.

       

      vPeriod_Test = chr(39)&date(max(PERIOD),'MM/DD/YYYY')&chr(39)

       

      However, the following does not seem to work:

       

      if($(vPeriod) = $(vPeriod_Test),1,0), I'm getting a 0 telling me that the two variables are not in the same format. How can I get these two in the same format?

       

      Any help would be appreciated. Thanks

        • Format question
          Erich Shiino

          Hi,

          you can make vPeriod_Test = date(max(PERIOD),'MM/DD/YYYY')

          or

          vPeriod_Test = text(date(max(PERIOD),'MM/DD/YYYY'))

           

          and to compare them, you can use:

          if('$(vPeriod)' = '$(vPeriod_Test)',1,0)

           

          If it doesn't work like this, you can try to compare them using number formats:

          vPeriod_Test = num( date(max(PERIOD),'MM/DD/YYYY') )

          and

          if( num( date#('$(vPeriod)','MM/DD/YYYY') = $(vPeriod_Test),1,0)

           

          Hope this helps,

           

          Erich

            • Format question

              Thanks Eric for the prompt reply. It worked. Let me provide some more context on my problem. I have a chart that sum sales on the most recent period.

              Currently, vPeriod is defined as vPeriod = '07/05/2011'. So my chart utilizes this variable in the following formula:

              Sum (PERIOD = {$(vPeriod)}>} Sales).

               

              The problem is, everytime I refresh the source data I have to manually update the Period. I'm trying to write a formula on the PERIOD variable such that it ONLY takes the max PERIOD.

               

              Note PERIOD field is in 'MM/DD/YYYY' format.

               

              Thanks


              Vishal

            • Format question
              Nagaian Krishnamoorthy

              Try this which seems to work.

               

              Let vPeriod = '07/05/2011';

              Let vPeriod_Test1 = date(vPeriod,'MM/DD/YYYY');