Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 215 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
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.
Answers
-
This is a BI forum ...
0 -
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.
0 -
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?
0 -
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.
0 -
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
0 -
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 ...)
0 -
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.
0 -
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.
0 -
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
0 -
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.
0
