Crystal report formula - to derive max year from parameter

[veerapureddy]

Hi,

We have a Crystal report where user enters 'Calendar month' as 10/2012(from) and 09/2013(to) and the parameter name is ZTEST_VAR.
From the entered value, we would like to derive 2013 as fiscal year. We have created the below formula, it is returning 2012 instead of 2013. Please advise what is wrong with the below formula.

DateValue(Maximum ({?[ZTEST_VAR]}))

Please note, I have created custom style for the 'Fiscal year' field to just print 'Year' only.

Best Regards,
Nanda.

 

[Charliy]

if your parameter type is a string, then "10/2012" > "09/2013" so the Maximum would pick up the from date.

Either make the parameter yyyy/mm format, or use two separate To and From parameters to get around this.

You would still need to have a formula that translates a date to a fiscal year. I use the following function for that:


Function (datevar Tdate)
// WhatFY - What Fiscal Year does this date fall in
// Input: Tdate, the date to be tested
// Output: Fiscal year as a 4 digit string
// Example: WhatFY(2/3/2003) = 2003 WhatFY(10/4/2003) = 2004
if month(Tdate) < 7 then totext(year(Tdate),'####') else totext(year(Tdate)+1,'####') ;

 

[veerapureddy]

Thank you Charliy,

I can't change it to YYYY/MM now as it involve considerable changes. I think the simple workaround(may not be technically proper solution) for this is using 'Minimum' and this will resolve all my scenarios as I will be interested with only 'Year'. This will work even If I enter 01/2012 - 12/2012(Output would be '2012') or 10/2012 - 09/2013(output would be '2013').

Best Regards,
Nanda.

 

[Charliy]

Try this: instead of Maximum({?[ZTEST_VAR]}) try {?[ZTEST_VAR]}[2]

A range parameter is an array, so you should be able to access the second element that way.

 

[BettyJ]

If the from and to dates are separated always by and and to date always comes second as you have mentioned in the first post, try
[tt]split(split({?CalendarMonth},"and")[2],"/")[2][/tt]