Former Member

### Date Calculations in SAP BW

I need assistance in defining a solution to calculate relative dates in the universe.

The goal is to create pre-defined date filters for rolling periods u2013 13 days, 13 Weeks etc and I am hitting a roadblock with them. What is the MDX equivalent for AddMonths/AddDates types of functions? Or alternatively how do you perform such calculations without having to go back to the BW query designer? I also need know a way to identify current date in the universe.

I have been experimenting by creating a sequence number in the BW Query that will subtract the Calendar Date from Jan 01 1900 and provide a continous number. The issue has been that this number needs to be an INT not NUMC as BW defaults to for me to subtract the relavent dates. And when you create this number as a Key Figure (non additive Key figure in SAP) - It does not allow addition or subtraction operations. When you add or subtract it results in Cartesian product. When you multiply or divide it gets the right results. So I am confused on why one would work but other does not.

Please advice a proven way to calculate relative dates or help me trouble shoot the above situation with the sequence number.

P.S: I have referred to the Best Practice Guide & OLAP universe guide but could not synthesize this information from them. There are no specifics related to calculations on Key figures such as the one described above.

10|10000 characters needed characters exceeded

• Former Member
Nov 19, 2009 at 10:23 AM

Hi,

Current date function exists in MDX,its called Now()

Unfortunately this function is not supported By SAP whereas it is by Microsoft.

MDX expression for rolling periods can be expressed like this:

`[0CALDAY].currentmember.lag(13)`

: Return the current day in the 0CALDAY characteristic minus 13 days.

You can do the same with year, quarter, month or week:

`[0CALYEAR].currentmember.lag(13)`
`[0CALQUARTER.currentmember.lag(13)`
`[0CALMONTH].currentmember.lag(13)`
`[0CALWEEK].currentmember.lag(13)`

To compute rolling periods you need expressions like YTD (Year to date), QTD, MTD, WTD like this:

`SUM(YTD([0CALDAY].currentmember.lag(13)), [Measures].[measure_definition])`

To have dynamic expression you can replace the number of periods (13 in the sample below) by a prompt expression like this:

`SUM(YTD([0CALDAY].currentmember.lag(@Prompt('Enter number of periods','N',,,))), [Measures].[measure_definition])`

Regards

Didier

10|10000 characters needed characters exceeded
• Amr Salem Former Member

Hello Didier

my requirement as follow,

lets have a scenario like my Sales orders

i've total amount for open sales order for this month

and total amount for open sales orders YTD (begining from this year up to this month)

and total amount for allllllllllllll open sales orders from any date any time, up to this month

how i can use lag function here?

thanks

Amr