Creating Year from Given Dates

[Anonymous]

Hello All,

I am having a field say CreatedOn, I am trying to create Academic Year with the following expression:

if(date(createdon,'DD/MMM/YYYY')>='01/Oct/2013' and date(createdon,'DD/MMM/YYYY')<='31/Aug/2014', 'AY 2014',

     if(date(createdon,'DD/MMM/YYYY')>='01/Sep/2014' , 'AY 2015')) as @AcademicYear

It working but I am not getting my data accurate.

Could some body suggest why it is?

Is their any syntax issue?

Regards,

Balraj

[MK_QSL]

Provide sample data or apps...

[jvitantonio]

Hi, Try this:

CALENDAR:
LOAD
CreatedOn,
...
if(createdon >= YearStart(createdon, 0,10) AND createdon <= YearEnd(createdon,0,10), YEAR(YEARSTART(createdon,0,10))+1) as @AcademicYear ;

I hope it helps.

JV

[MarcoWedel · Accepted solution]

you are comparing dates as strings so the resulat fo the comparison is not what you intended to do.

try like

if(createdon>=Date#('01/Oct/2013', 'DD/MMM/YYYY') and createdon<=Date#('31/Aug/2014', 'DD/MMM/YYYY'), 'AY 2014',

     if(createdon>=Date#('01/Sep/2014', 'DD/MMM/YYYY') , 'AY 2015')) as @AcademicYear

hope this helps

regards

Marco

[Anonymous]

You are comparing numeric dates to text dates.

For your hard coded dates use something like :

     date(date#('01/Oct/2013' ,'DD/MMM/YYYY') ,'DD/MMM/YYYY')

[Anonymous]

Thanks Marco

[Anonymous]

Or something like this may be more elegant :

=if ( (month( today())  ) >=  10 , year(today()) , ( year(today() ) - 1 ) )

[Anonymous]

Bill,

thanks for your response.

I am following a cycle from September to August. For this fiscal year we do not have September, so we are following from Oct- Aug.

But for next fiscal year we will be following Sept-Aug.

I have CreatedOn Field with Date values, is it possible to create Dynamic fiscal year from this field with the cycle Sept-Aug
??

Regards,

Balraj