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
Solved

Fiscal Years In Web Intelligence - Business Objects

Posted on 2014-02-12
7
3,925 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

792 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