• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4762
  • Last Modified:

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!
0
tracyms
Asked:
tracyms
  • 3
  • 2
  • 2
1 Solution
 
mlmccCommented:
This formula of yours should be the FY

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

Open in new window


If you just want the 2 digit year

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

Open in new window



Quarters - assumes July is in Q1

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

Open in new window


Thirds - Assume July is in T1

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

Open in new window

 
mlmcc
0
 
Jim HornMicrosoft 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
 
tracymsAuthor 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mlmccCommented:
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
 
tracymsAuthor 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
 
mlmccCommented:
Sure.

mlmcc
0
 
Jim HornMicrosoft 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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now