Month to Date and Year to date calculation

[Former Member]

Hello Experts,

It would be great If you could provide the formula to implement in BO universe or in the Reporting level for the below requirement.

MTD, LastMonth, Year to Date, Last to LastMonth and examples are below.

Last Week:

Eg: Monday through Sunday

Description: Based on Todayu2019s date, select the Monday of the previous week for the start date and Sunday will be the end date.

Example:

If Today is May 22, 2009

The result would be, Monday = May 11, 2009, Sunday = May 17, 2009

MTD:

If Today is May 22, 2009

I need a result MTD = May 1, 2009 u2013 May 21, 2009

LastMonth:

If Today is May 22, 2009

I need a result LastMaont = April 1, 2009 u2013 April 30, 2009

Last to LastMonth:

If Today is May 22, 2009

I need a result LastMaont = March 1, 2009 u2013 March 31, 2009

Thank you.

[Former Member · Accepted solution]

Nisniki,

Here are the methods using WebI:

MTD:

Create a local variable "MTD Start"

=RelativeDate(LastDayOfMonth(RelativeDate(CurrentDate();-30));1)

Create a local variable "MTD End"

=currentdate()

LastMonth

=LastDayOfMonth(RelativeDate(CurrentDate();-30))

Year to Date

create a local variable "YTD Begin"

=ToDate("01/01/"+FormatNumber(Year(CurrentDate());"####");"mm/dd/yyyy")

(reuse "MTD End" or build a "YTD End" variable same as "MTD End")

Last to LastMonth

(similiar to MTD, but using "60" versus "30" to move back two months versus moving back 1 month.

To perform this stuff in the universe requires an understanding of the SQL syntax for the paricular vendor your are working with. ANSI SQL agrees to represent and compute dates uniformly, however, the functions to make it happen varies by the vendor.

Thanks,

John

[Former Member · Accepted solution]

John,

Thank you very much for the reply. Can you help me in this. I have Date parameter and Period Parameter(Two Values Weekly and Monthly). If I Select Date Parameter 04/23/2009 and I select Period as Weekly, then I should get the records Month to date. I should see the records from 04/01/2009 to 04/23/2009 and If I select Monthly as Period then I should see 01/01/2009 to 04/23/2009.

Is this possible in WebI?

Thank you

Nisniki

[Former Member · Accepted solution]

Nisniki,

Is this possible in WebI? 

Possibly, however, it would be more efficient in the Universe. The reason I say that is all records would be sent over to WebI, then you would have to apply some type of filtering for your report, however, you would lose time and efficiency by having all records come over to WebI and then perform the filtering.

The features you want in your report will be better implemented at the Universe level. I believe you would get better performance, and can use prompts and filters in the Universe to more efficiently solicit from the user the parameters, then apply those parameters using date functions, which will build a more efficient where clause in the resultant SQL code.

Thanks,

John

[Former Member · Accepted solution]

Thank you very much John..