12 Replies Latest reply on Jul 28, 2016 4:53 AM by Gianni Ceresa

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

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.

• ###### 1. Re: Starting date = 1st day of a specific month (financial year)

This is a BI forum ...

• ###### 2. Re: Starting date = 1st day of a specific month (financial year)

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.

• ###### 3. Re: Starting date = 1st day of a specific month (financial year)

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?

• ###### 4. Re: Starting date = 1st day of a specific month (financial year)

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.

• ###### 5. Re: Starting date = 1st day of a specific month (financial year)

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

• ###### 6. Re: Starting date = 1st day of a specific month (financial year)

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

• ###### 7. Re: Starting date = 1st day of a specific month (financial year)

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.

• ###### 8. Re: Starting date = 1st day of a specific month (financial year)

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.

• ###### 9. Re: Starting date = 1st day of a specific month (financial year)

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

• ###### 10. Re: Starting date = 1st day of a specific month (financial year)

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.

• ###### 11. Re: Starting date = 1st day of a specific month (financial year)

Hi All,

I have found the Fiscal year option in the date folders. My issue is sometimes the date does not have any folder: we cannot chose which format of the date we want date, week, fiscal,...So would it be possible to apply a formula or filter on my date and say 'I want to have the current financial year' OR 'I want to start from the first day of the current financial year'?

I know with TIMESTAMPADD we can do first day of month, first day of week, etc. can we do first day of financial year?

Thank you very much.

• ###### 12. Re: Starting date = 1st day of a specific month (financial year)

If you read the answers to the thread you find it!

An OBIEE formula giving you the fiscal year start from any given date is posted on the 6th answer...

And again: it's all about math how to get it, not really an OBIEE issue as the tool gives you the functions to add/subtract periods to a date etc.