Previous month - last day

[Former Member]

Hi.

Problem: I need to get the previous month last day, no matter which date I open the report.

Example 1: I run the report on the 13th February 2008, and I want the report give me the last date of the previous month, in this example: 31th January 2008.

Example 2: I run the report on the 1st February 2008, and I still get 31th January 2008.

I have several calendar dimensions to use:

Date (numeric), Day (numeric), Month (numeric), Monthname (text).

I hope this is possible to do without altering the universe or write SQL in hand. Thanks in advance!

[Former Member · Accepted solution]

Andy,

Here is the WebI code to take a date and make it represent as the last day of the month prior to that date:

=RelativeDate(ToDate("01/"+FormatDate([Andy Date];"MM")+"/"+FormatDate([Andy Date];"yy");"dd/MM/yyyy");-1)

Here are some notes working inside out:

- Using [Andy Date] extract the month and year portion, then force that to a new date-type using the first day of the month (todate("01/"+(month of Andy Date) "/" (year of Andy Date);"dd/MM/yyyy")

- use RelativeDate to take the new date and give the minus 1 (substract) argument

Hope this works out for you. I did some testing on my end and was satisfied with the results, but more often than not have worked this problem on the SQL server side versus WebI. It's good to see WebI fles its muscles sometimes too.

Thanks,

John

[Former Member · Accepted solution]

Hi John,

Im getting this error no matter what I try:

Missing ';' before argument in function 'ToDate' at position 26. (Error: WIS 10062)

Am I doing something wrong?

- Andy

[Former Member · Accepted solution]

Andy,

I'm not sure what's up with the error you're getting, I would say to try to copy/paste directly into the variable editor formula portion and then substitute "Audit Start Timestamp" for your date. However, I need to provide a re-attack on the formula. Yesterday during testing I didn't notice a "problem", however, today I'm noticing that instead of getting 9/30/08 I'm getting 9/29/08. Same formula, just a different day of the month (today is Oct 3 and yesterday was Oct 2). I think that there are some inaccuracies in using RelativeDate that BusinessObjects has previously acknowledged and will be fixed in fixedpacks to 3.0 (and permanently fixed in 3.1), if my memory serves correctly (I lost a lot of my tech support notes for my BO account back in July when SDN took over the Tech Support Web pages -- nothing was ever transferred from the BO legacy support system for individuals like me, pretty sad, and sadder that I didn't keep my own running history, but now I've learned).

Anyhow, RelativeDate doesn't calculate properly as it seems to compute based on minute ticks versus "day" ticks, so we need to pile another function on to the formula -- LastDayOfMonth.

Here is an updated version that should work for you:

=LastDayOfMonth(RelativeDate(ToDate("01/"+FormatDate([Audit Start Timestamp];"MM")+"/"+FormatDate([Audit Start Timestamp];"yy");"dd/MM/yyyy");-1))

Thanks,

John

[Former Member · Accepted solution]

Nice!

It works like a charm!

I create a messure, called "Day" which contains the code:

=CurrentDate()

I then create a messure called "LastDay" which contains the code:

=LastDayOfMonth(RelativeDate(ToDate("01/"+FormatDate([Day];"MM")+"/"+FormatDate([Day];"yy");"dd/MM/yyyy");-1))

Thanks!

- Andy

[former_member212749]

Hi,

Hope you can use the Relative Date() and

LastdayofMonth()..

Regards

Prashant

[Former Member]

I had hoped I could to something matematicly like this:

I would want to solve it something like this (ill use March (3) as an example):

Take month number (3 for march) - and add 11 = 14

When number value is higher than 12, then substract 12 to the total. 14 - 12 = 2 (February)

Another example:

January (1) + 11 = 12. (Since 11 is not above 12, its not substracted by 12.)

February (2) + 11 = 13 (whis is above 12, therefore substract 12) = 1.

[former_member212749]

Hi ,

You can create a varible like this

=CurrentDate()-(DayNumberOfMonth(CurrentDate()))

This would be some thing like this..please check in the formula tab..this will work

Regards

Prashant

[Former Member]

Good idea. I tried this, but I get an error.

"The expression/sub-expression at position 1 uses an invalid data type. Specify a valid data type. (Error: WIS 10037)"

?

[former_member212749]

Hi Andy,

Use the Currentdate() as database specific..Like insted you user Today() or Now()

Regards

Prashant

[Former Member]

Im sorry, but I dont get it.

How can I manipulate, example my (Date) dimension?

=CurrentDate([date])-(DayNumberOfMonth(CurrentDate()))

Also produce an error. Can you give me a few examples on how to use the mentioned string?

[former_member212749]

Hi Andy,

Create a New Variable

paste the Below one

=CurrentDate()-(DayNumberOfMonth(CurrentDate()))

and let me know the error please let me know which Database are you using.

Regards

Prashant

[Former Member]

When creating a new variable I get the following error:

The expression/sub-expression at position 1 uses an invalid data type. Specify a valid data type. (Error: WIS 10037)

Im running on a SQL DB.

[former_member212749]

Hi Andy,

Can you try this

=[Date]-(DayNumberOfMonth([Date]))

Regards

Prashant

[Former Member]

I still get errors. (Invalid identifier 'Date' at position 1. (Error: WIS 10022))

Now I tried using only measures I created myself.

I created a measure called Date, which look like this:

=CurrentDate()

I then pull in the measure and modify the string to the sugested, and then I get this error: (Invalid identifier 'Date' at position 1. (Error: WIS 10022)).

Would it be possible to create the function from scratch, not using any Dimensions, measures and such...?

[Former Member]

Hi

try this one

Oracle last day previous month -

to_char(trunc(last_day(sysdate-27)),'yyyymmdd')

Regards,

sreeni

[Former Member]

Andy,

Do you want to get the previous month last day on WebI or SQL Server? In SQL server you'll have to parse the current-date (getdate()) to make it look like mm'/''01''/'yyyy - 1 (that is the month, an arbitrary day of 01, the year, then substract all of that by 1 (1 day) and you should be home free.

Thanks,

John

[Former Member]

I want to show the last day of last month in Webi. If possible I would rather not do any SQL by hand.

None of the suggested things work though.

I have a date = CurrentDate()

And I want to know the last day of last month from CurrentDate().