COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: esanray on 07 Jun 2011 03:23:32 AM

Title: Filter fiscal year .
Post by: esanray on 07 Jun 2011 03:23:32 AM
How to filter fiscal year to date like  1 april to till date - 1
i am try this:  To_Date ('01/04/2011','DD/MM/YYYY') AND  (SYSDATE - 1)
but the problem is that next year the filter is filter starting from 01/04/2011 not from 01/04/2012
can you please suggest me?
Title: Re: Filter fiscal year .
Post by: PRIT AMRIT on 07 Jun 2011 03:49:09 AM
To_Date ('01/04/'+to_char(extract(year,{sysdate})),'DD/MM/YYYY')
and
SYSDATE - 1

But what if the month is Jan-2012, Feb-2012 and Mar-2012, then you Fiscal Year should be 1 april 2011 to till date - 1 , correct?

If so, then you have to write a condition something like below,

If ( to_char(extract(month,{sysdate})) in ('Jan','Feb', 'Mar'))
then (To_Date ('01/04/'+to_char(extract(year,{sysdate})-1),'DD/MM/YYYY')
and
SYSDATE - 1)
else (To_Date ('01/04/'+to_char(extract(year,{sysdate})),'DD/MM/YYYY')
and
SYSDATE - 1)

Hope it does make sense?