Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 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
Using Current_Date to Create Financial Year Quarters
Hi all,
I am running this SQL in SQL Developer and it gets the results as I expected. I want to generate the same thing in OBIEE column formula 12c Version which returns the value in Financial Quarter. All I am trying to display the number of financial quarters to date in financial year.
Select
Case when trunc(sysdate) between ('01-JUL-2018') and ('30-SEP-2018') then 'Financial Quarter: 1'
Else
Case when trunc(sysdate) between ('01-OCT-2018') and ('31-DEC-2018') then 'Financial Quarter: 1 - 2'
Else
Case when trunc(sysdate) between ('01-JAN-2019') and ('31-MAR-2019') then 'Financial Quarter: 1 - 3'
Else
Case when trunc(sysdate) between ('01-APR-2019') and ('30-JUN-2019') then 'Financial Quarter 1 - 4'
END
END
END
END AS FIN_QTR from Dual;
OBIEE Column formula:
Case when Current_date between ('01-JUL-2018') and ('30-SEP-2018') then 'Financial Quarter: 1'
Else
Case when Current_date between ('01-OCT-2018') and ('31-DEC-2018') then 'Financial Quarter: 1 - 2'
Else
Case when Current_date between ('01-JAN-2019') and ('31-MAR-2019') then 'Financial Quarter: 1 - 3'
Else
Case when Current_date between ('01-APR-2019') and ('30-JUN-2019') then 'Financial Quarter 1 - 4'
END
END
END
END
Thanks in advance.
Omar
Answers
-
The syntax of your case in OBIEE is not correct, you only need one case statement, see ->
https://gerardnico.com/dat/obiee/obis/logical_sql/case
Also, ideally your quarters should be part of your time dimension and populated by your ETL - time dimension is a cornerstone dimension for me, if that is sub-optimal then it will have a big negative impact.
You might want to have a look at this for some examples of calculating various time based attributes.
OBIEE: Various Calculations for Date
Finally from your description; quarter 1 - 4, it looks like you really want cumulative values, in which case see ->
https://it.toolbox.com/question/showing-last-4-quarters-data-091311
0 -
Hi,
Without going into the details which Robert already covered, it's worth having a look at the CASE WHEN syntax (both SQL and OBIEE): it accepts multiple WHEN statements, so you definitely need to consider getting rid of your embedded CASE WHEN and have single unique CASE WHEN with the 3 (or 4 in case you need to manage a date out of range) statements.
CASE
WHEN .... THEN ....
WHEN .... THEN ....
WHEN .... THEN ....
ELSE ....
END
EDIT: just saw that was actually what Robert posted first But that CASEs hurt my eyes so much I missed it
0 -
Hi Gianni,
if the edit was aimed at me then no need, I am 'bigger' than the points awarded herein, if you take the gamification seriously then you have probably forgotten why you started playing the game...
if it was aimed at others then; - "Honi Soit qui mal y pense"
And I know exactly what you mean on the case statement, from writing epm business rules I got into the habit of having the most used IF option first, so the unnecessary nesting really hit me in the eye!
0 -
Build a proper calendar dimension ...
0
