Last month end date based on current date

[Former Member]

Hi,

How to show last month end date based on the current date.

Eg:

Current date = "08/26/09"

Var- Last Month End Date = "07/31/09" etc...,

Please help me how to get it...

Thank You!

[former_member207342 · Accepted solution]

HI

Please use following formula.

=RelativeDate(CurrentDate();-DayNumberOfMonth(CurrentDate()))

--Kuldeep

[Former Member · Accepted solution]

Hi Kuldeep thanks for your reply.

I tried the same but if you enter current date as 6/30/09 then it showing up 5/30/09 , but it is wrong, it should be 5/31/09.

Any other thoughts, please,...,

Thank You!

Edited by: BOCP-BOE on Aug 26, 2009 11:24 AM

[former_member207342 · Accepted solution]

Hi

I tried your date as,

=RelativeDate('6/30/2009';-DayNumberOfMonth('6/30/2009'))

O/p: 5/31/09

It works fine. Let me know how you are trying, and exact requirement

--Kuldeep

[Former Member · Accepted solution]

Good to hear that it worked for you. but not for me.

I tried like this:

1st::

1. var1= ToDate("06/30/09","MM/dd/yyyy")

2.Var2= RelativeDate([Var1];-DayNumberOfMonth([Var1]))

result: 5/30/09

2nd:

RelativeDate('6/30/2009';-DayNumberOfMonth('6/30/2009'))

result: 5/30/09

Am working on SAP OLAP cubes.

Please help me where am going wrong....

Thank You!

[former_member207342 · Accepted solution]

Where are you performing these operations. I hope its on Web Intelligence Report side.

I tried your way. It works fine.

May be you can check your cube for last date of July by using mdx QUERY.

I have a sample universe on Oracle. on which i am running these date operations.

Regards,

Kuldeep

[Former Member · Accepted solution]

Hi,

Trylike this Var2= RelativeDate(Var1;-DayNumberOfMonth(Var1)) +1 in webi

or in deski there is a Date finction(LastMonth End<Date>)

Cheers,

Suresh Aluri.

[Former Member · Accepted solution]

Hi,

Trylike this Var2= RelativeDate(Var1;-DayNumberOfMonth(Var1)) +1 in webi

or in deski there is a Date finction(LastMonth End<Date>)

Cheers,

Suresh Aluri.

[Former Member · Accepted solution]

Kuldeep , please tell me how to see MDX Query??

Thanks for reply Suresh.

Do you think it works...?? RelativeDate(CurrentDate();-DayNumberOfMonth(CurrentDate())) +1.

please test it from your end...,

It's not happening for all date selection. It's failing for 1st and last day selection only. like 6/1/09 or 6/30/09.

Adding of 1 to it is not good idea. Hope you got it.

In Web-I we have LastDayOfMonth() function...

Am trying to finding out any other way to do it... please someone help me.

Thank You!

Edited by: BOCP-BOE on Aug 26, 2009 5:20 PM

[Former Member · Accepted solution]

Hi,

It depends on your requirement

=RelativeDate(CurrentDate() ,-DayNumberOfMonth(CurrentDate())) +1 -> o/p is 01/08-2009

=RelativeDate(CurrentDate() ,-DayNumberOfMonth(CurrentDate()))

I have tried the above formula in BOXI R3 DeskI and its workin fine for me. i.e I got 7/31/2009 as o/p.

Cheers,

Suresh Aluri.

[Former Member · Accepted solution]

Yes Suresh, It's working for Currentdate() .

Please test it for 6/1/09 or 6/30/09 date. See above posts to understand it clearly.

Thank You!

[Former Member · Accepted solution]

Hi,

For your case I have created 2 var as

Current date: = ToDate("06/01/09" , "mm/dd/yy") or Current date: = ToDate("06/30/09" , "mm/dd/yy")

Last Month end date: = =RelativeDate(<Current Date> ,-DayNumberOfMonth(<Current Date>))

o/p for Last Month end date: 5/31/2009

Cheers,

Suresh Aluri.

[Former Member · Accepted solution]

Thanks Suresh .

But still am not getting date as 5/31/09, it showing as 5/30/09.

I used what you posted only but not succeeded.

please help me what may be the cause.

BO-XI3.x and SAP BW.

Thank You!

[Former Member · Accepted solution]

BOCP-POE:

what version of BusinessObjects are running? Certain versions of WebI of XIr2 have a problem with accuracy using Relativedate() due to a leap year computation it is one day off, but this problem does not occur in the DeskI version. It was supposed to be fixed in the 3.0 arena....

Thanks,

John

[Former Member · Accepted solution]

Thanks John for your reply. Am in BO-XI3.x only and OLAP Cubes.

I don't know why it's not working for me.

Please help me...

Thank You!

[former_member207342 · Accepted solution]

for MDX to query SAP OLAP.

you can refer this post, it has all the info.

--Kuldeep

[Former Member · Accepted solution]

Thanks Kuldeep and all,

I got solution:

=If(Month([Current Date]) InList("January";"March";"May";"July";"August";"September";"December");LastDayOfMonth(RelativeDate([k5] ;-31));If(Month([Current Date]) InList("April";"June";"October";"November");LastDayOfMonth(RelativeDate([k5] ;-30));If(Mod(Year([Current Date]);4)=0;LastDayOfMonth(RelativeDate([k5] ;-29));LastDayOfMonth(RelativeDate([k5] ;-28)))))

Thank You!