Previous month - last day
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!
John Sanzone replied
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))