AnsweredAssumed Answered

Calculated Field Not Using The Functions Provided

Question asked by nriordan on May 27, 2010
Latest reply on Jun 3, 2010 by Robert Ensinger
To simplify the filtering process and to be able to group data according to Fiscal Year, I want to add an attribute that explicitly states the Fiscal Year on the project object. What is the best method for doing this?

Our Fiscal Year does not match the standard calendar year and thus would need to be set on an if-then-else type formula which is not provided through the application. I am thinking the resulting value would be a 4 digit number rather than a date field according to the following:
If the Month is January - June, use the current year
If the Month is July - December, use (current year + 1)
Fiscal Years runs from July - June and refernced based on the June year. The SQL below shows this process using the current date as the value.

select CASE
WHEN to_number(to_char(sysdate,'MM')) > 6 then to_number(to_char(sysdate,'YYYY'))+1
ELSE to_number(to_char(sysdate,'YYYY'))
END
from dual;

The date values that this would be calculating off of would be static so there is no need to have this be dynamic. An example is the approval date of the project. The approval date is recording as a date and then would want the FY calcuation to be done based on that. If you have a project approved on 8/2/05, then the Fiscal Year value would be 2006.

Outcomes