Convert Calendar Year to Fiscal Year

building reports webintelligence
1

Hi,

Can someone help me in getting a formula to convert Calendar Year to Fiscal Year at Report level and Universe Level.

Calendar Year --> January 2011 to December 2011
Fiscal Year --> April 2011 to March 2012

Incase the topic is available in Search, can someone guide me to the link please.

Thank you

iamlearning (BOB member since 2010-12-12)

2

You will have to have a calendar table in your database as each one has their own Fiscal calendar if I am right.

Jansi :india: (BOB member since 2008-05-12)

3

Hi,

Does you have you got the answer for your Calendar year to Fiscal year Conversion, if Yes can u please share it.

I have the same requirement but our Fiscal year Starts from October2016 to September 2017 ( as FY2016). So i need t modify the formulas as per my requirement.

Thanks in Advance

coll (BOB member since 2011-11-03)

4

At report level, something like

if formatdate([Date variable];“yyyyMM”) between(“201610”;“201709”) then “FY2016”

would work. You’d need to extend it to handle other years. Or alternatively

“FY” + formatnumber(if monthnumberofyear([Date variable])<=9 then year([Datevariable])-1 else year([Datevariable]);“0000”)

mikeil (BOB member since 2015-02-18)

5

As Jansi said seven years ago, a calendar table in your database is the best way to support this requirement. There are many articles online about how to create one for your database - Oracle, SQL Server, DB2, etc.

If you have a date and want to calculate it at the report level, then a simple piece of If logic would allow you to convert any calendar date into a financial year, like this:

=If(Month([Transaction Date])<=9;Year([Transaction Date])-1;Year([Transaction Date])

That will help you out short term but longer term I would strongly recommend the calendar table as it will prevent errors and allow you to simply drag another object into the query rather than using variables and bloating the reports. :slight_smile:

6

Hi Mark & Jansi,

Thanks for Quick Reply. Can anyone suggest me the formula for below

We dont had Calendar object defined, and we can’t request them to create a new one, but we have Week, Week number, Year and Quarter objects in Universe

Using Quarter objects we need to get the last 4 weeks data from the current date for these Fiscal Year and also for Previous Year of the same last 4 weeks.
Our Fiscal Year starts from October to September, and we are in FY2018 (Fiscal Year) as of date.

We have to do it using QUARTER Object. (Current Year Q4 to Next Quarter Q3) and (Previous Year Q4 to Present Year Quarter Q3)

We have source as ABAP data and we need to write formula in HANA SQL in Universe, or Webi(if both are provided, that would be much helpful).

coll (BOB member since 2011-11-03)