converting a date

[former_member318041]

Dear experts,

I have trouble converting a date.

There is a given field in WEBI called "date_now" and I would like to convert this date and write it into a new variable called "date_old".

The following canversions should be done:
- reduce the year by 1
- set the day to 01

For example:

date_now = "07.12.2016" ---> date_old = "01.12.2015"
date_now = "23.05.2016" ---> date_old = "01.05.2015"
date_now = "17.03.2014" ---> date_old = "01.03.2013"

It seemed quit easy in the beginning, but now I am struggling ...
Any help is appreciated!

TIA,
Henning

[amitrathi239 · Accepted solution]

what is your BO version?If it's BVI 4.1 sp06+ then you can follow below formula.

=ToDate("01."+FormatDate(RelativeDate([date_now];-12;MonthPeriod);"MM.yyyy");"dd.MM.yyyy")

[former_member318041]

Hi Amit,

thank you for this great way to convert the date!

The problem was on my side as the "Locales and Time Zone" were set to German and I made a mistake when translating "MonthPeriod" into german.

Have a great day,

Henning

[former_member318041]

Hi Amit,

thank you for your answer!

I am working on "SAP BusinessObjects Business Intelligence 4.1 Support Pack 7".

When I try to implement it on my side, I receive the following error:

Any idea?

BR,

Henning

[amitrathi239]

what is the error message?

what is datatype of "date_now" object? date or string?

if it's string then try this.

=ToDate("01."+FormatDate(RelativeDate(todate([date_now];"dd.MM.yyyy");-12;MonthPeriod);"MM.yyyy");"dd.MM.yyyy")