nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Starting date = 1st day of a specific month (financial year)

3283263

Hi there,

Are their any formulas (like timestampadd, iff...) to be able to tell to the CRM to select only the opportunities starting from the 1st of October till the current financial year?

For example if we are the 20 July 2016, the CRM will select all opportunities from 1 October 2015 => 20 July 2016.

Our financial year is from 1 October N to 30 September N+1.

Thank you very much.

Find more posts tagged with

Accepted answers

All comments

Thomas Dodds

This is a BI forum ...

3283263

Hi Thomas,

I know this is a BI forum. That is the reason why I am posting my question here. On this same forum you can see similar request like First Date of a Fiscal Year. Unfortunately this code does not answer my question.

Gianni Ceresa

Hi,

I guess Thomas answer is related to this :

5856e31e-0817-4b2f-8e87-b60e7d87d636 wrote:

... to be able to tell to the CRM to select...

So what is that thing with a CRM? Do you want that filter in an analysis in OBIEE or are you trying to do something else?

3283263

Hi Gianni,

I am working on a report on CRM Oracle and need an analysis in OBIEE in order to be able to get the report that I want.

3283263

CRM Oracle is a copy of OBIEE.

Now, my question is:

Are their any formulas (like timestampadd, iff...) to be able to have an automated filter on date from the 1st of October till the current financial year?

For example if we are the 20 July 2016, the date will be filtered from the 1 October 2015 => 20 July 2016.

Our financial year is from 1 October N to 30 September N+1.

I really need a genius idea to help me to get that. Please

Gianni Ceresa

Well, of course there is a formula to get it ...

It's all about math!

A possible way to do it:

to_datetime('01/10/'||CAST(YEAR("Time"."T00 Calendar Date") + CASE WHEN MONTH("Time"."T00 Calendar Date") < 10 THEN -1 ELSE 0 END as VARCHAR(4)), 'dd/mm/yyyy')

You can of course also use TIMESTAMPADD to go back to the first day of the month first and then go back to the previous month of October.

(Just don't try to subtract directly a number of day from the current date as will have to manage by hand the 29th February when it exists ...)

Thomas Dodds

CRM threw me off ...

Why not have a date grained table that has your financial year details? then you don't have to mess around with adding and subtracting dates and performing functions in analysis columns. A simple TO_DATE at the year grain then gets you exactly what you want.

Joel Acha

Ideally, you'd have a time dimension in which you fiscal year attributes are defined and then quite simply, all you'd need is to build your query/report based on a filter of time dimension records where the fiscal year is 2016.

Gianni Ceresa

So, summarizing what Thomas And Joel are saying : adapt your time dimensions by adding the fiscal year informations and then model an alternate hierarchy in your OBIEE for the fiscal year and ... done !

Just use the standard OBIEE aggregations and time series functions

3283263

Hi All,

Thank you very much.

Ideally I would like to set up my financial year in CRM but I have no idea how to do that. Would you be able to explain me the procedure?

Thank you.