Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Provide sample data or apps...
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
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
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')
Thanks Marco
Or something like this may be more elegant :
=if ( (month( today()) ) >= 10 , year(today()) , ( year(today() ) - 1 ) )
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