This is a BI forum ...
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.
I guess Thomas answer is related to this :
... 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?
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.
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
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 ...)
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.
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.
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
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?
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.
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.