Default Date in prompts - previous year quarter first day

[Former Member]

Hello Experts,

I am trying to create default date prompts for webi report where we need to display the begin date as first day of previous year’s quarter and that should be dynamically calculated each time report is refreshed.

Eg: Todays date: 30/9/2018

Expected Date: 01/7/2017

I have created universe parameters and tried using RelativeDate(CurrentDate();-4;QuarterPeriod) however this returns the today-365 day’s date.

The sql dateadd functions do not work and also we can’t use any blx objects as we are defining it as universe parameter.

Can anyone help with how we can achieve this using universe parameters or any other way?

[ayman_salem · Accepted solution]

You can do the following:

define following variables:

1. Your formula above as a variable

1stVar =RelativeDate(CurrentDate();-4;QuarterPeriod)

2 . calculate first month of Qurater

1stMofQ =Quarter(CurrentDate()) * 3 - 2

3. Define the variable to calculate the diff in the month between the current month and the first month of the current quarter (from 1stVar or from currentDate() it is the same)

2ndVar =MonthNumberOfYear(CurrentDate()) - [1stMofQ]

4. finally:

dateOf1stDayofQofLastYear = RelativeDate([1stVar];-[2ndVar];MonthPeriod)

I hope this will help you

[ayman_salem]

Hi Tina,

In order to implement the solution in the universe, you need to define the following variables to calculate the "Quarter, Current month"

In addition, there is no RelativeDate function at the universe level. Use add _months instead.

....

Objects:

CDate: sysdate (or any other date)

CMonth: to_number(to_char(@Select(CDate), 'mm' ))

Quarter: ceil(@Select(CMonth)/3)

1stMofQ: @Select(Quarter) * 3 - 2

final result:

RDate: add_months(@Select(CDate), - ( 12+ ( @Select(CMonth)-@Select(1stMofQ) )))

Then adjust the day to first day of month

FRDate: @Select(RDate) - to_number(to_char(@Select(RDate), 'dd' )) + 1

....

hope that will help you

[nscheaffer]

I addressed this scenario a few years ago by creating a Calendar universe. Here's how I did it...

https://blogs.sap.com/2014/02/11/first-day-of-previous-week-with-a-twist/

It has been working well ever since.

Noel

[Former Member]

Hi Ayman,

Thanks a lot.. Since I need to display this as default value of begin date I will need to implement the solution at universe. With first approach it returns correct month and further cascading the formula to return the relative date by day period helps to resolve this.

Regards,

Tina

[ayman_salem]

Another Solution:

1. calculate first month of Qurater

1stMofQ =Quarter(CurrentDate()) * 3 - 2

2. define DateAsString

DateAsString ="01/"+ [1stMofQ]+"/"+FormatNumber(Year(CurrentDate())-1;"#")

3. finally

dateOf1stDayofQofLastYear2 = ToDate([DateString];"dd/MM/yyyy")