BusinessObjects Board

How to add Month to A Date objects in WEBI

other Xcelsius - Crystal Dashboard Design
#1

Hi Gurus

Anyone can advise the best way to Add month to a Date object in WEBI, the following code only take care of 1 month

=If(MonthNumberOfYear([YourDate]) InList( 1;3;5;7;8;10); RelativeDate([YourDate];31);If(MonthNumberOfYear([YourDate]) = 2; RelativeDate([YourDate];28 );RelativeDate([YourDate];30)))

If YouDate = 1/3/2011 and we want to add 3 months for e.g. to become 1/6/2011. If applying the above code, i.e. 30xVariable (use input control to allow user to key in the month), the result may be 1 or 2 day less than the actual result, for e.g. 30/5/2011

seowkian (BOB member since 2010-10-26)

#2

Hi

Basically what I meant is that if BOXI got an equivalent Edate commands (Excel), which will take care the number of days of Months when adding months to a particularly date

i.e. if date = 02/09/13

Add 3 months = 2/12/13
Add 4 months = 2/01/14
Add 5 months = 2/02/14

Thanks !

seowkian (BOB member since 2010-10-26)

#3

Try this:

todate(DayNumberOfMonth([My Date])+"/"+If(MonthNumberOfYear([My Date])+[numberofmonths]>12; MonthNumberOfYear([My Date])+[numberofmonths] - 12 ; MonthNumberOfYear([My Date])+[numberofmonths])+"/"+FormatNumber(Year([My Date]);"#"),“dd/MM/yyyy”)

This will work sometimes. Issues come up when the target month doesn’t have as many days as the source month. What do you want to happen in that scenario? For example, adding 3 months to November 30?

theknowtank (BOB member since 2011-04-20)

#4

Actually…that solution sucks.

ALSO… need more information. Is it possible to be adding more than 12 months? That makes it harder.

-tkt

theknowtank (BOB member since 2011-04-20)

#5

Ask your universe designer to do it, way easier using SQL!

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

#6

Hi,

Thanks for all the suggestion.

Actually the I wanted to tap up all the WEBI experts whether they had coded in Report level using variable and WEBI commands and functions, getting IT team to hardcode in the Universe/Data mart level is the last resort as we do not want to introduce non-frequent measure backend.

Any other suggestions out there ?

seowkian (BOB member since 2010-10-26)

#7

If this is pure webi, then getting a mod to move this thread to the correct forum might get it a bit more exposure? :wink:

Debbie

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