Format question

[#1]

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

[erichshiino]

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

[nagaiank]

Try this which seems to work.

Let vPeriod = '07/05/2011';

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

[#4]

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