Oracle Analytics and AI

Use reporting period instead of fiscal year

TKQ5Q

Hi Sir

We have an existing fiscal year that start from 1 July to 30 Jun.

One of the data table has an "issued date".

Is it possible to filter issued date for reporting purpose using some formula

Example:

issued date from 1 April 2020 to 31 March 2021 = Reporting Period 2020

issued date from 1 April 2021 to 31 March 2022 = Reporting Period 2021

If so, how do I do it in Enterprise Edition 11.1.1.7 analysis?

Thank you.

Find more posts tagged with

Analytics

Comments

[Deleted User]

Just like a Fiscal Year this has to be modeled into your time dimension.

TKQ5Q

@Christian Berg-0racle

can it be done via formula like if else. is there any example that i can read on?

Thank you

Gianni Ceresa

Sure you can, but it's something that has good chances to give you issues in the future, when everybody forgot it was there.

Your fiscal year is a basic information that should be part of your time dimension as Christian said.

If you still want to use formulas, in OBIEE you have functions to give you the current date, the year for a date (current date) and the month number for a date (once again using the current date).

You also have CASE WHEN doing the if-else logic: if the month is <= 5 then year-1 else year, job done.

[Deleted User]

Beaten by Gianni due to meeting, but: Sure. You can do just about anything in formulas.

But think about it: code gets interpreted for each single row that is being parsed. Every. Single. Row. If you have 5 it will be fast. If you have 5'000 it will be a bit slower. If you have 5m? Probably so slow you can forget it. 5b? Forget it.