Solved

Fiscal Years In Web Intelligence - Business Objects

Posted on 2014-02-12
7
3,808 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
 
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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
 
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 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Abstract Express Replacement Software 12 28
Query to capture 5 and 9 digit zip code? 4 20
T-SQL:  Collapsing 9 25
Loops and updating in SQL Query 9 29
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

777 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