Current FY Quarter +1

Joe Choueiri-Oracle

Hi

I need to create an analysis to show deals for the current quarter (Jun 01 - Aug 31) and then another analysis to show current quarter +1 then current quarter +2 ...I could not figure it the timestampadd formula that I need to use for that...would you be able to help.

Thanks

JE

Gianni Ceresa

Do you really need to use timestampadd?

A quarter isn't a date, it's a string like '2020 Q3'. In your time dimension you are supposed to have them as part of the hierarchy: year > quarter > month > date.

So when you talk about:

Joe Choueiri-Oracle wrote:...need to create an analysis to show deals for the current quarter (Jun 01 - Aug 31) and then another analysis to show current quarter +1 then current quarter +2 ...

You mean an analysis for '2020 Q3', '2020 Q4' and '2021 Q1' or you are really using dates in a way or another making up some kind of quarter figures by an aggregation into columns formulas in the analysis?

Joel

The ideal way to do this is to have a date/time dimension that has all the date attributes you need to capture like the previous quarter, current month, current year, week number, the list goes on. If these are all pre-calculated then you can just use it rather than attempting to evaluate these at runtime.

Robert Angel

re: Joel's suggestion see -> http://onlyobi.blogspot.com/2013/07/create-date-dimension-using-sql.html

Joe Choueiri-Oracle

Hi @Gianni Ceresa

No I dont need to use timestampadd...all I want is to build 4 tables, mainly with quarters

1. Top 10 deals closing in current quarter
2. Top 10 deals closing in (current quarter and next quarter)
3. Top 10 deals closing in (current quarter and next 2 quarters)
4. Top 10 deals closing in (current quarter and next 3 quarters)

Thanks

Joe

Robert Angel

Hi Joe,

your immediate need might be what you describe but what Gianni / Joel are pointing you to is one of the cornerstones.

A sound time hierarchy begets so much useful functionality; -

- Time based measures; Ago, Period Rolling, Total To ...Month / Quarter, Year

- Time based drills

- Alternative hierarchies - Fiscal

Really, there are two paths you can take with OBIEE, the tactical where you achieve what you set out to achieve but never use the tool to the full potential, or the strategic where you exploit the full value add that OBIEE can provide over your data layer

With the time measure in place you can then build the rolling quarter functionality that you describe.

https://gianniceresa.com/2017/01/time-series-functions-why-how-what-where-obiee/ https://tekslate.com/create-measure-using-periodrolling-function

http://obieetutorialguide.blogspot.com/2012/03/time-series-functions-obiee.html

So, you really do need a sound time dimension, it enables so much.