cancel
Showing results for 
Search instead for 
Did you mean: 

Last month end date based on current date

Former Member
0 Kudos

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!

Accepted Solutions (1)

Accepted Solutions (1)

former_member207342
Contributor
0 Kudos

HI

Please use following formula.

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

--Kuldeep

Former Member
0 Kudos

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
Contributor
0 Kudos

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
0 Kudos

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
Contributor
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
0 Kudos

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
Contributor
0 Kudos

for MDX to query SAP OLAP.

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

--Kuldeep

Former Member
0 Kudos

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!

Answers (0)