BusinessObjects Board

Help with defining a Current Quarter Measure

building semantic layer
#1

All,
Relativly new at some of the tricks of the trade.
Oracle 11g and BO3.1

I am trying to build a Current Quarter object using a Calendar table.
I have sucessfully built and used Current and Previous day and month measures to build reports.
I am trying to build one that looks at the Quarter Start date for the current day and returns the Key date for all rows in the current quarter. This would then be used as a selection against my Fact table.

I am using Trunc(sysdate) for current date, add_months(trunc(sysdate,‘MM’), -1) for Previous Month Start date and calculating the end dates accordingly.
In my report I am selecting the Business Date on my fact table using the between operator and objects, selecting my Start and End Month objects.
I can do the same using selected dates off my calendar table to create any sized range.
What I am now trying to do is build a Calendar Quarter object and select the rows off my Fact where the Business Date is in the list of dates on the Calendar table where the Quarter is the current quarter.
The following query returns data from my fact table for all dates in the current quarter.

SELECT BUS_DT FROM DW_TXN_FCT WHERE
BUS_DT IN (SELECT KEY_DT FROM DW_CAL
WHERE QRTR_ST_DT = (SELECT QRTR_ST_DT FROM DW_CAL
WHERE KEY_DT = to_date(sysdate,‘dd-MON-rr’) )
);
I am having trouble building the object that will satisfy this. Once that is created I know I can copy it to use a date prompt to select any other quarter( or year ect)

Thanks for your help!
Greg

amosgreg (BOB member since 2012-01-27)

#2

Did you look at this thread ?
https://bobj-board.org/t/152613

Here is the syntax to create a measure by using directly fact table:

SUM(CASE WHEN DW_TXN_FCT.BUS_DT >= trunc(sysdate,'Q') and DW_TXN_FCT.BUS_DT <= add_months(trunc(sysdate,'Q'),3) - 1  THEN
     <YourFact.MeasureColumn>
ELSE
	  0
END)

Here is the syntax to create a measure by using calendar table and I expect your calendar table is linked to your fact table in your Universe:

SUM(CASE WHEN CalendarTable.Date >= trunc(sysdate,'Q') and CalendarTable.Date <= add_months(trunc(sysdate,'Q'),3) - 1  THEN
     <YourFact.MeasureColumn>
ELSE
	  0
END)

[OR]

if you would like to define a Pre-defined condition in the universe, declare two objects in the universe:
CurrQuarter_Start_Date

trunc(sysdate,'q')

CurrQuarter_End_Date

add_months(trunc(sysdate,'q'),3) - 1

Use the above objects like below in your condition

CalendarTable.Date between @prompt('Enter Qtr Start Date:','D','ClassName\CurrQuarter_Start_Date',mono,free,,) and @prompt('Enter Qtr End Date:','D','ClassName\CurrQuarter_End_Date',mono,free,,)

Note: You may have to look for exact prompt syntax.

BO_Chief :us: (BOB member since 2004-06-06)

#3

Thanks BO_Chief, Yes I had seen the relative date logic thread.
I will play around with what you have given me but where I was getting stumped was I have generated a Calendar table with a number of predefined dates including the quarter start and end. I would like to drive off the calendar rather than calculate my own so that if there is ever a need to adjust the start/end date range it can easily be done by lookup.
My calendar table is linked to my fact table by single day date.
I am struggling not with the selection of the quarter in question but with then retrieving all the single days data based on the Day date in the set returned by the selection of the quarter start and/or end date.

This is easy to do with the SQL sub-select inside another sub-select(in my origional post) but I am having trouble figuring out this same behavior in BO. Am I trying to make it too hard?

Again I have no issues defining a hard date calculation as you provided (thank you!) but I am trying to drive off my calendar table for flexability.

Thanks
Greg

amosgreg (BOB member since 2012-01-27)

#4

Hmmm… Why don’t you create a Derived table with your SQL ?

[OR]

Check this link
http://howtobi.wordpress.com/2010/11/11/is-it-my-time-yet/

BO_Chief :us: (BOB member since 2004-06-06)

#5

Thanks BO_Chief I will check it out.
I also had a thought that maybe I am trying too hard for a single object and need to create an alias of my Calendar table and bump against that for my quarter date based on a prompted or selected date.
I’ll play with this today and let you know.
Thanks again for your thoughts!

Greg

amosgreg (BOB member since 2012-01-27)