Need formula for Lastweek, MTD and LastMonth

building semantic layer
1

Last Week:
Eg: Monday through Sunday
Description: Based on Today’s date, select the Monday of the previous week for the start date and Sunday will be the end date.
Example:
If Today is May 22, 2009
The result would be, Monday = May 11, 2009, Sunday = May 17, 2009

MTD:
If Today is May 22, 2009
I need a result MTD = May 1, 2009 – May 21, 2009

LastMonth:
If Today is May 22, 2009
I need a result LastMaont = April 1, 2009 – April 30, 2009

Last to LastMonth:
If Today is May 22, 2009
I need a result LastMaont = March 1, 2009 – March 31, 2009

Pls provide your suggessions on this.

Thanks!!!

srikumar_busobj (BOB member since 2009-02-19)

2

Welcome to B:bob:B!

Which is the underlying database?

Jansi :india: (BOB member since 2008-05-12)

3

Database is DB2.
BO Version is XIR3.1

Expecting your suggessions or solutions for the same.

Thansk1

srikumar_busobj (BOB member since 2009-02-19)

4

Search B :mrgreen: B with the keyword MTD. You can find many topics. One such is this. Make sure you use DB2 syntax.

Jansi :india: (BOB member since 2008-05-12)

5

Hi Srikumar,

I too have the same requirement…

Please let me know how u have implemented…

Thanks,
vaasu

vasu237 (BOB member since 2007-02-19)

6

Hi All,

I know to get it in DB2 but when I’m trying it in BO its throwing an Error , any suggestions? below is the query which i ran in Db2

LAST WEEK=

with temp(day1) as
(
select date(Current Date) from sysibm.sysdummy1
union all
select day1-1 day from temp where day1>=date(Current Date)-7 day
)
select Date(day1) from temp
where dayofweek(day1) between 1 and 6

sujathaparuchuri (BOB member since 2007-03-01)

7

Hi Sujatha,

Please find this link…Hope u will get some idea :slight_smile:

Thanks,
Vasu

vasu237 (BOB member since 2007-02-19)

8

Thank you guys for all the help

sujathaparuchuri (BOB member since 2007-03-01)