BusinessObjects Board

1st & last day of month based on month selected

building reports webintelligence
#1

Hi All,

I am an infant in BO. I want to create a variable in which I need to compare Order Creation Date with First Day of Month and Last Day of Month. I have gone through the posts here regarding this, I found huge no of posts on calculating the 1st & last day of month based on a date value. But, I don’t have any date objects in the universe level, instead I have only Month Name/Month Number objects in the universe level (as it is a monthly report)

So, How to create 1st day & last day of month based on the month selected.That is, If I run the report for FEB 2011 (Prompt values) I want the 1st date & last date of FEB 2011 & not the first & last days of Current month

I am working on BOXI R 3.1

Thanks in Advance,
Mitch

mitchamdon :australia: (BOB member since 2011-07-05)

#2

First you need to convert your Date number to Date.
Use the help of relativedate,Daysnumberofmonth, and lastdayofmonth function to calculate firstday and lastday of selected month.

kool :nepal: (BOB member since 2005-04-15)

#3

Thanks for the instant reply Kool. I need the whole date including month & year, not just the date no.

That is, if I select Feb 2011 as my prompt values, I need the outputs as 01/02/2011 & 28/02/2011 as my 1st & last date values respectively.

mitchamdon :australia: (BOB member since 2011-07-05)

#4

Use these formulas :

  1. First Day of the Month :
    =ToDate(UserResponse(;“Mmm yyyy”)

  2. Last Day of the Month :
    =LastDayOfMonth(ToDate(;“MMM yyyy”))

ds41user :india: (BOB member since 2007-09-03)

#5

Hi bhupendra,

Sorry to ask like this, I wasn’t able to create the variable with the formula you gave.

I am having 2 parameters in my Query

  1. Month Prompt with “Inlist” operator
  2. Year with Equal To operator

Variable I created

  1. V_First Day of Month=ToDate(UserResponse(“Reporting Month”);“Mmm”) “with out using year” — ouput I am getting 1/1/1973 year wrong as I haven’t given yyyy in the variable.

  2. If I add yyyy in the variable
    V_First Day of Month=ToDate(UserResponse(“Reporting Month”);“Mmm yyyy”) then it throughout error #ERROR

Can you please help me with this.

Thanks in Advance,
Mitch.

mitchamdon :australia: (BOB member since 2011-07-05)

#6

You need to bring in Month as a Result Object.

You can then define a date object as :

V_First Date = ToDate([Reporting Month] + " " + UserResponse(“Reporting Year”), “Mmm yyyy”)

V_Last Day = LastDayOfMonth(V_First Date)

ds41user :india: (BOB member since 2007-09-03)

#7

Create two objects in Universe:

YearC=to_char(@Select(Time\Year))

MonthC=to_char(@Select(Time\Month))

Hide These Objects and create two conditions containing @prompt function referring the List of values of above defined objects

Now in report create two variables as

First Day of Month=“01-”+[MonthC]+"-"+[YearC]

Last Day Of Month: LastDayOfMonth(ToDate([FirstDayOf Month];“dd-MM-yyyy”))

bob2210 (BOB member since 2010-09-23)

#8

Thank you very much Bhupendra,

Your formula worked.

Sorry for the late reply, I was away for Christmas.

Thank you once again,
Mitch.

mitchamdon :australia: (BOB member since 2011-07-05)

#9

Removed as per this topic.

gigi (BOB member since 2010-03-27)

#10

Moderator note:
@Gigi: Please, answer directly instead of posting a generic link to YOUR blog :?

Andreas :de: (BOB member since 2002-06-20)