on 10-01-2018 8:58 AM
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?
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
95 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.