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


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


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


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


          Hope this helps,



            • 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.




            • Format question
              Nagaian Krishnamoorthy

              Try this which seems to work.


              Let vPeriod = '07/05/2011';

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