BusinessObjects Board

Condition in Universe

building semantic layer
#1

Hi,
I need to put a condition in Universe for getting last years last quarter sales. Any thoughts please… The database is Oracle.

sraju123 (BOB member since 2010-08-02)

#2

Look at the various date arithmetic functions available in oracle:

SELECT sysdate FROM dual;

gets you the current date and time

SELECT trunc(sysdate) FROM dual;

returns today’s date with time set to 00:00:00

SELECT trunc(sysdate)+12/24 FROM dual;

returns today’s date + 12 hours

SELECT trunc(sysdate, 'yyyy') FROM dual;

returns the first day of today’s year at midnight, e.g. 01/01/2010 00:00:00

SELECT add_months ( trunc(sysdate, 'yyyy') , -1) FROM dual;

returns first day of today’s year (=01/01/2010 00:00:00) MINUS one month --> Dec 1st 2009 00:00:00

With this you can create your own time filters in the universe such as:

FactTable.Date >= TRUNC(sysdate, 'yyyy') 
AND FactTable.Date  < TRUNC(sysdate)+1

to retrieve this year’s data, etc.

Andreas :de: (BOB member since 2002-06-20)

#3

Thanks for throwing some light on this issue. But I’d appreciate if you can give me the code for obtaining last years last quarter sales.

Thanks
SRaju123

sraju123 (BOB member since 2010-08-02)

#4

Please, put some elbow grease to it… :wink:

Andreas :de: (BOB member since 2002-06-20)

#5

It’s in the FAQs for this forum… :wink:

Debbie

Debbie :uk: (BOB member since 2005-03-01)

#6

And this sticky topic can help too :slight_smile:

Marek Chladny :slovakia: (BOB member since 2003-11-27)