Calendar Year column into Fiscal Year Column

Question

Hi

I have a Calendar Year and Calendar Month columns in my Table. But i want Fiscal Year and Fiscal Month columns in my report.

(Ex: Last year 09-01-2015 to 08-31-2016 comes under Fiscal Year 2016 and Sep Month comes under Fiscal Month 1, Oct month comes under Fiscal Month 2 etc...)

How can I change the logic..

Thanks in Advance.

Gianni Ceresa · Answer

As Joel said ...

Yes, there are many ways to convert a calendar date into a fiscal one, the question is: what are your fiscal calendar rules?

And based on your answer the issue happen when you try to have nicely formatted columns "like FY 2016-1" instead of "01.09.2016" etc.

That's why you are supposed to generate it in your DB, providing a translation of a normal date into the fiscal one (so in general you add both calendars to the same table) and in OBIEE you model it as alternate hierarchies allowing the user to decide when building an analysis which one he wants to use.

Of course you can create it with some nice CASE WHEN and other kind of formulas, but honestly ... do it as clean as possible ...

Joel Acha · Answer

Your best bet is to add these fiscal columns to your date dimension as anything else will have issues with either performance or with the date hierarchy.

Fiscal calendars tend to vary by organisation as well.

GSR · Answer

I don't have any Fiscal year and month columns in DB. We have only calendar date columns.

Is there any work around or logic to convert Calendar year column into Fiscal Year column.