# Fiscal Years In Web Intelligence - Business Objects

I'm trying to create a fiscal year variable in Web Intelligence. Our fiscal year is 7-1 of current year to 6-30 of next year. I want this variable to based on a specific date that's in the universe (Uvdate). I want this variable to change as the calendar year changes - meaning automatically.

So, if my universe date (Uvdate) is 2-12-14, this falls in our current fiscal year FY-14 (between 7-1-13 and 6-30-14).  If the Uvdate is 2-12-13, then the fiscal year would be FY-13 (between 7-1-12 and 6-30-13).

Additionally, I would like to create two more variables for these fiscal years - for Quarters (every 3 months) and for Thirds (every 4 months). I actually have all these variables but they are hard coded dates.

I ran across a few of these formulas that look close to want I want but don't know how to get the information I need:

="FY"+Right(FormatNumber(If(Month(CurrentDate())="January";Year(CurrentDate());Year(CurrentDate())+1);"#");2)+" Q"+Quarter(CurrentDate())

="FY"+ If(MonthNumberOfYear(CurrentDate())>=7;Year(CurrentDate())+1;Year(CurrentDate()))

Thanks for any assistance you can provide!
LVL 1
###### Who is Participating?

Commented:
This formula of yours should be the FY

``````="FY-"+ FormatNumber(If(MonthNumberOfYear([Uvdate])>=7;
Year([Uvdate])+1;
Year([Uvdate][Uvdate]));"####")
``````

If you just want the 2 digit year

``````="FY-"+ Right(FormatNumber(If(MonthNumberOfYear([Uvdate])>=7;
Year([Uvdate])+1;
Year([Uvdate][Uvdate]));"####");2)
``````

Quarters - assumes July is in Q1

``````="Q-" + If(MonthNumberOfYear([Uvdate])<=3;"3";
If(MonthNumberOfYear([Uvdate])<=6;"4";
If(MonthNumberOfYear([Uvdate])<=9;"1";"2")))
``````

Thirds - Assume July is in T1

``````="T-" + If(MonthNumberOfYear([Uvdate])<=2;"2";
If(MonthNumberOfYear([Uvdate])<=6;"3";
If(MonthNumberOfYear([Uvdate])<=10;"1";"2")))
``````

mlmcc
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
If your fiscal logic is sufficiently complex that a function or three won't do it, I have an article out there on How to Build your own Calendar Table, which you can expand by adding columns for your fiscal year-quarter-month, writing T-SQL in that, and then you can consume it everwhere with a JOIN on this table.
0

Author Commented:
mlmcc,

Your variables worked. The [Uvdate] was listed twice for FY the variables but once I took one out it was fine. Thank you!

Jim Horn,

I only have user level access to create variables/queries for reports that are in our departmental universe so I am limited to what I can create/modify.
0

Commented:
If this is something needed on numerous reports, it would be better to add the calculation to the universe as details of the date object.  Doing it there will make it easier to change if the FY ever changes.

mlmcc
0

Author Commented:
Thanks, I will submit a request to our database admin. I assume I can also change the months/years in your formula to reflect changes as well?
0

Commented:
Sure.

mlmcc
0

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Tracy - I just kicked out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table:  Fiscal Years.   Let me know if this helps you, and if yes please click on the 'Good Article' button and provide some feedback.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.