Solved

Fiscal Years In Web Intelligence - Business Objects

Posted on 2014-02-12
7
3,685 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 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 65

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 100

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
 

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 100

Expert Comment

by:mlmcc
ID: 39858874
Sure.

mlmcc
0
 
LVL 65

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now