BusinessObjects Board

Dynamic Date

building semantic layer
#1

hi all,
I need to add dynamic date in where condition in date field (Table1. DATE field). Dynamic date is always between 1st November and Sysdate. If I ran the query in Dec 2009, data supposed to be display between 1st Nov 2009 and Sysdate (2009 Dec sysdate). Similarly if I ran the report in Feb 2010, data supposed to be display between 1st Nov 2009 and sysdate (2010 Feb sysdate), id I ran the same report in Mar 2011, data supposed to be display between 1st Nov 2010 and Sysdate (Mar 2011 sysdate) and so on…how to write this dynamic where condition…?

Philip (BOB member since 2006-03-02)

#2

What db do you have?

Marfi :poland: (BOB member since 2006-12-18)

#3

Try here for some ideas:-

https://bobj-board.org/t/152613

Mak 1 :uk: (BOB member since 2005-01-06)

#4

Thanks for your useful information on dynamic date. But how to implement my requirement? I do not see any clue on it. I am using oracle db.

Philip (BOB member since 2006-03-02)

#5

One option would be to maintain a period table where you can maintain the start and end dates. The start date would be the 1st date of analysis and the end date would be the date itself. You can alias the fact table and join the alias with this period table with a between clause.

Now any measure pulled from the fact table would automatically sum up the values between the start and end dates.

This is very similar to doing YTD or MTD type computations. I have described a possible design approach for that on my blog http://howtobi.wordpress.com/2010/11/05/how-to-perform-ytd-or-any-period-to-date-design-in-the-universe/. This approach is the same as what I have described above.

hbanerjee :singapore: (BOB member since 2010-08-17)

#6

Philip,

Is 1st Nov the start of the financial year in your organisation?

If so, do you have a calendar table that matches up the financial year to the individual calendar dates?

#7

Hi Mark,
Yes. Our FY start from 1 Nov and I have a calendar table that matches up the financial year to the individual calendar dates

Philip (BOB member since 2006-03-02)

#8

Then one option is to create a YTD column.

Create it as a boolean and have your ETL process update it each night.

Your YTD total is then simply Sum(fact_table.value_column*sales_calendar_alias.ytd_flag)