topic Re: Creating Year from Given Dates in QlikView

Creating Year from Given Dates

Anonymous — Wed, 03 Sep 2014 11:12:16 GMT

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

Re: Creating Year from Given Dates

MK_QSL — Wed, 03 Sep 2014 11:20:14 GMT

Provide sample data or apps...

Re: Creating Year from Given Dates

jvitantonio — Wed, 03 Sep 2014 11:20:26 GMT

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.

Re: Creating Year from Given Dates

MarcoWedel — Wed, 03 Sep 2014 11:22:34 GMT

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

Re: Creating Year from Given Dates

Anonymous — Wed, 03 Sep 2014 11:24:28 GMT

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

Re: Creating Year from Given Dates

Anonymous — Wed, 03 Sep 2014 11:31:22 GMT

Thanks Marco

Re: Creating Year from Given Dates

Anonymous — Wed, 03 Sep 2014 11:31:33 GMT

Or something like this may be more elegant :

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

Re: Creating Year from Given Dates

Anonymous — Wed, 03 Sep 2014 11:49:53 GMT

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