Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Calendar Year column into Fiscal Year Column

Received Response
23
Views
4
Comments
GSR
GSR Rank 3 - Community Apprentice
edited Aug 13, 2024 6:19PM in Oracle Analytics Cloud and Server

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.

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Do you have your fiscal year and month in your DB table?

    If not it's maybe the first thing to do: source them in your DB ...

  • GSR
    GSR Rank 3 - Community Apprentice

    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.

  • Joel
    Joel Rank 8 - Analytics Strategist

    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.

  • Gianni Ceresa
    Gianni Ceresa Mod

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