?
Solved

Fiscal Years In Web Intelligence - Business Objects

Posted on 2014-02-12
7
Medium Priority
?
4,419 Views
Last Modified: 2015-09-22
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
Comment
Question by:tracyms
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 39856141
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39856347
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
 
LVL 1

Author Comment

by:tracyms
ID: 39857989
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 101

Expert Comment

by:mlmcc
ID: 39858086
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
 
LVL 1

Author Comment

by:tracyms
ID: 39858112
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
 
LVL 101

Expert Comment

by:mlmcc
ID: 39858874
Sure.

mlmcc
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40989017
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question