Date Variables in Web Intelligence
Dear All,
Here in SAP BusinessObjects BI Web Intelligence Space, it is noted that many of us are searching/asking questions on Date/Time Dimensions.
I am submitting all necessary formulas for the same. Hope it is useful to all.
Adding related blogs here for SAP HANA Views as well.
How To…Calculate YTD-MTD-anyTD using Date Dimensions
Implementation of WTD, MTD, YTD Period Reporting in HANA using Calculated Columns in Projection
Implementation of WTD, MTD, YTD in HANA using Input Parameters only
Simple example of Year To Date (YTD) calculation in SAP HANA
Implementation of WTD, MTD, YTD in HANA using Script based Calc View calling Graphical Calc view
Applying YTD in SAP HANA with SAP BO Analysis Office
Much appreciated efforts by them. Thanks.
We need daily,monthly,quarterly & yearly date variables.
Before making any date variable please make a variable which holds current date.
Reason to make another variable for Current Date is, for validation purpose you can change date manually and then check it whether all other date variables are working properly or not.
(1) Current Date = CurrentDate()
(2.1) Current Year =If(Month([Current Date]) InList(“January”)) Then(FormatNumber(Year([Current Date])-1;”###”)) Else (FormatNumber(Year([Current Date]);”###”))
if you want to use Current Year for YTD variable then please use 2.2 formula or use 2.1
(2.2) Current Year for YTD=If(Month([Current Date]) InList(“January”;”February”;”March”)) Then(FormatNumber(Year([Current Date])-1;”###”)) Else (FormatNumber(Year([Current Date]);”###”))
(3) Current Fiscal Year =FormatNumber([Year];”####”) Where ([Year]=Year([Current Date]) And [Month] InList (1;2;3) And MonthNumberOfYear([Current Date]) InList (1;2;3;4) Or [Year]=Year([Current Date])-1 And [Month] InList (4;5;6;7;8;9;10;11;12) And MonthNumberOfYear([Current Date]) InList (1;2;3;4)
Or [Year]= Year([Current Date])And MonthNumberOfYear([Current Date]) InList (5;6;7;8;9;10;11;12))
where [Year]= Year of Your Transaction Date, and it is for Indian Fiscal Year
(4.1) Last Year =If(Month([Current Date]) InList(“January”)) Then(FormatNumber(Year([Current Date])-2;”###”)) Else (FormatNumber(Year([Current Date])-1;”###”))
if you want to use Current Year for LYTD variable then please use 4.2 formula or use 4.1
(4.2) Last Year for LYTD =If(Month([Current Date]) InList(“January”;”February”;”March”)) Then(FormatNumber(Year([Current Date])-2;”###”)) Else (FormatNumber(Year([Current Date])-1;”###”))
(5) Last Fiscal Year =FormatNumber([Year];”####”) Where ([Year]=Year([Current Date])-1 And [Month] InList (1;2;3) And MonthNumberOfYear([Current Date]) InList (1;2;3;4) Or [Year]=Year([Current Date])-2 And [Month] InList (4;5;6;7;8;9;10;11;12) And MonthNumberOfYear([Current Date]) InList (1;2;3;4) Or [Year]= Year([Current Date])-1 And MonthNumberOfYear([Current Date]) InList (5;6;7;8;9;10;11;12))
where [Year]= Year of Your Transaction Date, and it is for Indian Fiscal Year
(6) CYCM=Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);Concatenation(“-“;[Current Year]))
*Current Year Completed Month
(7) CYLM=Concatenation((Concatenation(Left(Month(RelativeDate(RelativeDate([Current Date];-DayNumberOfMonth([Current Date]));-DayNumberOfMonth(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))));3);”-“));If(MonthNumberOfYear([Current Date])=1 Or MonthNumberOfYear([Current Date])=2) Then (Right(FormatNumber(Year([Current Date])-1;”####”);4)) Else (Right(FormatNumber(Year([Current Date]);”####”);4)))
*Current Year Last Month
(8) LYCM=Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);Concatenation(“-“;[Last Year]))
*Last Year Completed Month
(9) YTM=Concatenation(If(MonthNumberOfYear([Current Date]) Between (5;12)) Then (Concatenation((Concatenation(“Apr”;”-“));Right(FormatNumber((Year([Current Date]));”####”);4))) Else(Concatenation(Concatenation(“Apr”;”-“);Right(FormatNumber((Year([Current Date])-1);”####”);4)));If(MonthNumberOfYear([Current Date])=5)Then(“”)Else(Concatenation(” to “;(Concatenation((Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);”-“));(If(MonthNumberOfYear([Current Date])=1) Then(Right(FormatNumber(Year([Current Date])-1;”####”);4))Else(Right(FormatNumber(Year([Current Date]);”####”);4))))))))
*Year Till Month
(10) LYTM=Concatenation(If(MonthNumberOfYear([Current Date]) Between (5;12)) Then (Concatenation((Concatenation(“Apr”;”-“));Right(FormatNumber((Year([Current Date])-1);”####”);4))) Else(Concatenation(Concatenation(“Apr”;”-“);Right(FormatNumber((Year([Current Date])-2);”####”);4)));If(MonthNumberOfYear([Current Date])=5)Then(“”)Else(Concatenation(” to “;(Concatenation((Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);”-“));(If(MonthNumberOfYear([Current Date])=1) Then(Right(FormatNumber(Year([Current Date])-2;”####”);4))Else(Right(FormatNumber(Year([Current Date])-1;”####”);4))))))))
*Last Year Till Month
(11) MTD=Concatenation(Left(FormatDate([Current Date];”dd/MM/yyyy”);2) ;Concatenation(“-“;Concatenation(Concatenation(Left(Month([Current Date]);3);”-“);Right(FormatNumber(Year(CurrentDate());”####”);4))))
*Month Till Date
(12) YTD=Concatenation(Concatenation(“Apr-“;[Current Year for YTD]);If(MonthNumberOfYear([Current Date])=4)Then(“”) Else(Concatenation(” to “;Concatenation(Concatenation(Left(Month([Current Date]);3);”-“);Right(FormatNumber(Year([Current Date]);”####”);4)))))
* Year Till Date
(13) Current Quarter
=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=1) Then(Concatenation(Concatenation(“Q4:Jan-“;[Current Year]);Concatenation(” to Mar-“;[Current Year]))) ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=2) Then(Concatenation(Concatenation(“Q1:Apr-“;[Current Year]);Concatenation(“to Jun-“;[Current Year]))) ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=3) Then(Concatenation(Concatenation(“Q2:Jul-“;[Current Year]);Concatenation(“to Sep-“;[Current Year]))) ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=4) Then(Concatenation(Concatenation(“Q3:Oct-“;[Current Year]);Concatenation(“to Dec-“;[Current Year])))
*based on Indian Fiscal Year
(14) Last Quarter
=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=1)
Then(Concatenation(Concatenation(“Q3:Oct-“;[Last Year]);Concatenation(” to Dec-“;[Last Year])))
ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=2)
Then(Concatenation(Concatenation(“Q4:Jan-“;[Current Year]);Concatenation(” to Mar-“;[Current Year])))
ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=3)
Then(Concatenation(Concatenation(“Q1:Apr-“;[Current Year]);Concatenation(“to Jun-“;[Current Year])))
*based on Indian Fiscal Year
(15) Last to Last Quarter
=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=1)
Then(Concatenation(Concatenation(“Q2:Jul-“;[Last Year]);Concatenation(” to Sep-“;[Last Year])))
ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=2)
Then(Concatenation(Concatenation(“Q3:Oct-“;[Last Year]);Concatenation(” to Dec-“;[Last Year])))
ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=3)
Then(Concatenation(Concatenation(“Q4:Jan-“;[Current Year]);Concatenation(“to Mar-“;[Current Year])))
ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=4)
Then(Concatenation(Concatenation(“Q1:Apr-“;[Current Year]);Concatenation(“to Jun-“;[Current Year])))
(16) Last Year Current Quarter
=If(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=1) Then(Concatenation(Concatenation(“Q4:Jan-“;[Last Year]);Concatenation(” to Mar-“;[Last Year])))
ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=2)
Then(Concatenation(Concatenation(“Q1:Apr-“;[Last Year]);Concatenation(“to Jun-“;[Last Year])))
ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=3)
Then(Concatenation(Concatenation(“Q2:Jul-“;[Last Year]);Concatenation(“to Sep-“;[Last Year])))
ElseIf(Quarter(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))=4)
Then(Concatenation(Concatenation(“Q3:Oct-“;[Last Year]);Concatenation(“to Dec-“;[Last Year])))
(17) Last Day(date) of Previous Month
=FormatDate(LastDayOfMonth(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));”dd-MMM-yyyy”)
Output: If Current Date=27/10/2015 then output is 30-SEP-2015
(18) Previous Day/Yesterday (Day-1)
=RelativeDate(CurrentDate();-1)
Output: If Current Date=27/10/2015 then output is 26/10/2015
(19) Previous Month
=FormatDate(LastDayOfMonth(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));”MM”)
Output: If Current Date=27/10/2015 then output is 09
Best Regards,
-Harshil J Joshi
I did not go through the entire doc. But I am confused at point #2.
Why for January, you are assigning the previous year to variable current year?
For Monthly Reporting Variable:
Current Year =If(Month([Current Date]) InList("January")) Then(FormatNumber(Year([Current Date])-1;"###")) Else (FormatNumber(Year([Current Date]);"###"))
Reason: It is monthly period, we are reporting for the month December in January at that time Year should be -1.
For Daily Reporting Variable:
Current Year = FormatNumber(Year([Current Date]);"###")
Reason: It is daily period, we are reporting for the same month which is currently running.
Thanks.
OK
Hi Arjit Das, I would like to know the steps to calculate the given month is falling over the First half of the year or second half of the year.. Please suggest me with an example.
Thanks
Vijay
How do you pass the month parameter - is it by month number or month name?
How do you define first & second half of a year ?
Assuming you have the month number, Jan-Jun = 1st half, Jul-Dec = 2nd half,
v_FirstOrSecondHalfOfYear = If([MonthNumber]<7;1;2)
Thanks Arjit Das for your prompt reply.
My requirement is to create a multi tab report for(Daily, monthly, quarter, half year and year ) based up on given date.
with the variable and formatdate() options i have derived all except Halfyear. I need to show the first given date is falling under First Half / Second Half on my half year report
v_FirstOrSecondHalfOfYear = If(MonthNumberOfYear([YourDateObject])<7;1;2)
Thank you Arjit Das.. it works fine after changing the datatype tonumber(). I have a query in open doc.. I have a requirement like, in excel sheet i have 1.report name, 2.report link (copied from webi ) and 3. required parameters to run.. when the user select the report name the link should automatically accepts the listed params and should display in the new window. Objective: it should not ask any parameters from end users. please help me out. Thanks.
Please send me some sample doc to review and to test locally.
Great work Harshil
Thank You very much Vijay.
Appreciate your efforts. Thanks for sharing.
Thanks Bijal.
Good job harshil..I shall use them soon..Bookmarked!
Regards,
AL
Thanks Anshu.
I think you need to be careful with this when you are talking about Fiscal Year. Not every company has the same Fiscal Year.
In addition to this, Webi formulae are a poor alternative to a well thought out calendar table implemented at the database level.
Final point, why use Inlist("January") when you can use ="January" instead?
Thank You Mark for your assistance. That's why I mentioned for Indian Fiscal Year Only, yes it is not for every company. We need these formulas on webi itself to show the Date,Month Name, Quarter Name for daily, monthly or quarterly reporting.
Yes you can.
Thanks a lot for your consideration.
Nice, keep it up. 🙂
Thank You Giri Prasad......much appreciated...
Hi Harshil,
Good information. Bookmarked and Liked. But what about date conversion and date difference ?
Can you please add the same in it ?
Hi,
Thank you.
For Date Conversion, please specify your dates which you want to convert or I suggest to open a new thread.
And I also request to please search before posting you may get your solution from
How to use formulae to change date and time formats in WebIntelligence
Webi Date Format
http://bobjblog.wordpress.com/2012/03/05/web-intelligence-date-formats/
Thanks a lot! Great Job
Thank you Sreedhar.
Good article ...Harshil
Can we find weekday between two dates?
Like no of days excluding sat and sunday between this two dates.
Hello Nirav,
Good to see you after a long time.
Yes definitely we can do it
[Day_Name_1]=DayName([Entered Date])
[Count]=If([Day_Name_1] InList("Saturday";"Sunday")) Then 0 Else Count([Day_Name_1])
and then you can SUM [count] variable.
Thanx harshil...
Good one...Thank you for sharing useful info.
Chandrakanth
Thank You Chandrakanth.
Hi Harshil
its too good doc.
Thanks for sharing.. 🙂 🙂
Regards
Waseem
Thank you wasem hassan. I appreciate your words. Thanks a lot.
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
Thank You WILLIAM MARCY,
It is glad to be part of your summary blog Webi 4.x tricks : summary. I really appreciate it.
Thank You.
Dear Team,
for
(3) Current Fiscal Year =FormatNumber([Year];"####") Where ([Year]=Year([Current Date]) And [Month] InList (1;2;3) And MonthNumberOfYear([Current Date]) InList (1;2;3;4) Or [Year]=Year([Current Date])-1 And [Month] InList (4;5;6;7;8;9;10;11;12) And MonthNumberOfYear([Current Date]) InList (1;2;3;4)
Or [Year]= Year([Current Date])And MonthNumberOfYear([Current Date]) InList (5;6;7;8;9;10;11;12))
where [Year]= Year of Your Transaction Date, and it is for Indian Fiscal Year
I am getting output as 2013 is it correct???
But I feel 2014 is the correct. I dont know where exactly I am going wrong.Plz help...
Regards,
Mahesh
Hello Mahesh,
In India, the government's financial year runs from 1 April to 31 March midnight. Example: 1 April 2013 to 31 March 2014 for the financial year 2013–14. In government form it shows 2013-2014, in general use it shows Fiscal Year 2013 (means 01-04-2013 to 31-12-2014).
Formula shows Fiscal Year =2014 from April-2014 on wards to March-2015.
Hope it helps.
If you want to show 2013-2014 then let me know.
Hi Harshil Joshi,
Thanks for your early reply, Ur clear explanation is superb I got it now.
Regards,
Mahesh
Thank You. I will update my document which will cover formula to show 2013-2014. As this is asked by many so.
Hello,
I have a requirement where I need to pulll anniversaries for employees based on Hire date. I have Hire date stored in the SQL Tables and I can pull that information.
I need to generate a report every month end and provide list of anniversary dates for the employees for that month ? How can I acheive this in WEBI ?
Hi Archana,
You have hire_date in SQL table, but where anniversary_date is stored ?
anniversary date, number of years is something that I need to calculate while generating the report for current month. For example if there are 2 employees whose Hire date is 3/1/2013 , 3/1/2012. output should be something like this
Emp Name Hire Date Anniv Date Num of Years
Ann 3/1/2013 3/1/2014 1
ted 3/2/2012 3/2/2014 2
BTW I dont have access to universe. So I need to create variables formulas at webi report level
Ok.
Please follow steps.
(1) Make a variable Anniversary_Month=MonthNumberOfYear([Ann_Date])
(2) Make a variable Current_Month=MonthNumberOfYear(CurrentDate())
(3) Make a variable Filter=If([Current_Month]=[Anniversary_Month]) Then(0) Else(1)
Now make a report as below.
Now make a filter as below
Now you will get list of employees who have anniversary in current month (say March)
Hope it helps.
Thank You so much for your quick responses. Really appreciate. One other question. I need to display number of years . How can I calculate difference between Hire Date Year to current Year?
Iam trying to use daysbetween but its giving me error ...
I am getting unrecognized input
=DaysBetween([Hire Date],CurrentDate())/365
It should work. Let me know the error. Also requesting you to please open a new thread for the same or search before you post. Thank you.
Hello Harshil,
Very useful document... Thanks for sharing...
Regards
Subbarao M
Thank You Very Much.
Hi Harshil,
Extremely good document - thanks for sharing.
How would I convert the dates under (6) and (7) to the following format dd/mm/yyyy i.e. 31/12/2013?
Hi Barry,
Thanks for your valuable words.
Do you want to convert dates from dd/mm/yyyy format to which one ?
May I know your exact requirement ?
Hi Harshil,
Very helpful document, I feel like every one bookmark this for further reference.
Good Job !!
-Sastry
Thank You Sastry.....
Hi Harshil,
I am facing some problem in date difference. It is not showing correct date difference. I have two date dimensions date1 & date2. When I perform DaysBetween function it is showing me wrong difference. Can you please help me solve this problem?
Hi,
I faced same issue. If you are making report on relational database then check at query level. your two date dimensions date1 and date2 should be Dimension Type ="Date". Just check it and let me know.
Thanks
Hi Harshil,
I have a date parameter JoinDate for example like
02/31/2010
04/22/1998 ....
I want to extract the Month, Day from Join Date and add current year . For example above 2 records would become 02/31/2014, 04/22/2014
How do I achieve this ? I dont want the above number to be changed to char because I need to compare to another date parameter to filter.
If I use the below year value is changing to number and no able to compare
=FormatDate([JoinDate];"MM/dd/")+FormatNumber(Year(CurrentDate());"####")
Hi Archana,
You want to extract date & month from your date JoinDate right?
You can use Left([joindate];4) and then format date.....!!!
Hi Archana,
For That You have to create below variables,
1. Month : = left(Jointdate)
2. Date := substr(Jointdate;4;2))
3. Year := Year(Currentdate())
4. Joint Date with current year :
=ToDate(Month+"/"+Date+"/"+Year;"MM/dd/yyyy")
OR
=FormatDate(ToDate(Month+"/"+Date+"/"+Year;"MM/dd/yyyy");"MM/dd/yyyy")
From this you will get a date format so that you can compare with other date.
let me know if it will not work....
Thank You,
Haresh Pipariya
thanks for the info ,its a great learning 🙂 from you.
Hi Harshil,
Thank you for sharing, it is very helpful.
I have a requirement to get the last 3 months based on the user input (Calmonth) through the prompt.
How should I go with this scenario ?
Your help will be much appreciated.
Thanks
Ram
Hi Ram,
You can use the formula for Last Month and based on that you have to make 2 more formulas for Last Month -1 and Last Month -2.
Please let me know if you face any problem in that.
Hi Harshil,
if you dont mind can you please give step by step for this solution. I have the same requirement in weeks. when user inputs data it should fetch last 5 weeks data. Can you be kind enough to give me step by step.
Many thanks,
Dev
Devadas,
Do you want this kind of structure ? ?
HI Harshil,
Sorry for the late reply, was on holiday, I want exactly same structure for 5 weeks, Can you please give me step by step cos I'm new to BOBJ
Thanks a million
Devadas
Respected Sir,
Thanks for this valuable info.
I just wanted to know how to subtract dates under same column and get the result on the next column.
Dear All,
I want the result for every Request id :- to subtract dates under column (Ac Create Date) and get the difference between dates in the format of "No of days" in next column (Delay). Tried Days Between but it doesn't gives result..Please help..
For eg:- Request Id:- 547926 II Ac Create Date:-6/10/14 II Dealy:-15 days( From the next below Ac Create Date)
Hi Fahad,
The format of two date should be same as "date". Please check.
If not then use FormateDate Function for the same.
Respected Sir,
I want the result for every Request id separately: - Subtract dates under column (Ac Create Date from the below date) and get the difference between dates in the format of "No of days" in next column (Delay). Tried Days Between but it doesn't gives result. Please help...
For e.g.:- Request Id: - 547926 II Ac Create Date:-6/10/14 II Delay:-15 days (From the next below Ac Create Date)
Dear Harshil,
I have just simplified my question for better understanding as I was unable to do in my previous question..Apologies for that..
Please help me to get the difference in dates coming under the same column.
I want the result in the format of No of days in the next column.
For eg:-
Date
No of Days (This must be the result after using your formula)
7/13/2014
2
7/15/2014
5
7/20/2014
10
7/30/2014
-41850
If below there's no date it must be Zero “0” in (No of days column), as I don't want error in my report like-41850.
Can you please help me with this formula...
Dear Sir,
I'm running a report from January 2014 till date.
I want a variable that helps me to show only the current week data.
Please help ASAP if available now.
Thanks In Advance
Hi Harshi,
Do we have any solution to show last 5 weeks of data from displaying last 52 weeks of data.
last 5 weeks of data from history
Thank you in advance,
suri
very hlpfull...thanks for sharing
Regards,
Rishabh
Hi Harshali,
we have the data from Bex and there is a resrtiction on query, no need to do the changes any thing in bex because that query was used by other WebI reports, so I need to do the changes in WebI level.
Rrequirement as follows
if I execute the report for single date it will Execute for 7 days, Example : If I
execute for 1/1/2000 then it will Execute for 7 days, like 1/1/2000 to 7/1/2000
Do the needy Harsha.
Regards,
Tulasi.
Hi Harshali,
I have a requirement like below
If I execute the report for single date it will Execute for 7 days,
Example : If i execute for 22/1/2010 then it will Execute for 7 days, like 22/1/2010to 28/1/2010
please do the needy help
Regards,
Tulasi
Hi Tulasi,
Which database are you using.
Like for informix database you can create a conditional object as:
@Select(calss/object) between (@Prompt('Entry Date:','D',,mono,free)) and (MDY(month(@Prompt('Entry Date:','D',,mono,free)),day(@Prompt('Entry Date:','D',,mono,free)),year(@Prompt('Entry Date:','D',,mono,free)))+7)
not sure but i think for SQLit would be something like below:
@Select(Your Date Object) between @Prompt('Date:','D',,mono, free) and dateadd(d,+7,@Prompt('Date:','D',,mono, free))
Give it a try might help solving your issue.
Thanks,
RS
Hi Rishabh,
We are using Bex, On top of that we have webi report, could you help on this
Thanks
Hi Tulasi,
Not sure it will work but you can try creating a variable in bex using offset function and defining the value range and then pull that variable in your report.It this also din't work then you can also go for customer exit.
Regards,
RS
Thanks Rishabh,
here we have restriction, i.e on ly do changes in Webi level only.
Hi Harshil,
Thanks for sharing valuable information .
I am having few queries on deski reports
In my report i am having 4 tabs.. 1 tab is fetching data for 2015 Jan,
2 tab is fetching data for 2015Feb ...data is not loaded for March,April ..etc for next months till dec.
But My month column shows only "2015 Jan" "2015 FEB".Once data is loaded for next month it shows"2015 march"
I need to implement formula for " march" .Exactly i am not aware when data is going to load .
and in query level ,we are using filter as "Current year"
Thanks,
Raghunath
Hi Harshil,
I have a requirement for Webi report as below.
Basically, i need to fetch YTD count per month. But, i don't want to include the count of current month last year.
PFA the screen shot. And please help me out.
TIA!!
Regarding date format in YYYYMM format.
Formulas:
Previous Month
=Left(FormatDate(RelativeDate(CurrentDate();-DayNumberOfMonth(CurrentDate()));"MM/dd/yyyy");2)
Previous to Previous Month
=Left(FormatDate(RelativeDate(RelativeDate(CurrentDate()
;-DayNumberOfMonth(CurrentDate()))
;-DayNumberOfMonth(RelativeDate(CurrentDate()
;-DayNumberOfMonth(CurrentDate()))));"MM/dd/yyyy");2)
Your output will be
Further you can do by if else statement
here is a much simple example . its much easier to understand
i have date object [Inventory PQI Date] , and then i have following variable
=If([Inventory PQI Date])= (CurrentDate())Then 1 Else 0
now when i see for todays date that is 6/20/2015 , it should say 1 , but its just saying 0
Also im putting the current date column also to show this report ran for that 6/20 date
Hi All, I would like to know the steps to calculate the given month is falling over the First half of the year or second half of the year.. Please suggest me with example.
Thanks
Vijay
Very good doc..
Its really very useful documents.
All level of Developers can be easily understand your documents.
Keep going
We are in BI 4.1 SP5 and have webi report based on HANA, The webi report has HANA input parameters as prompts when running the report. The Input parameters uses the date . Now the report has to be scheduled with date dynamically changing . The Universe is built on IDT and we haven't used derived table for passing prompts . Appreciate your inputs on this
Hi experts Appreciate response on same
Hi Ben Li,
Hope you have got your solution.
No Harshil, I havent received solution so far. Appreciate any input on this
Hi Sir,
your content is very helpfull for me.
I need some help to create variable for Current Quarter+Previous 4 months(before current quarter) in webi reports.
I tried many ways but I am unable to reach the requirement. Hope ill be get quick response.
Thanks In Advance...
Hi Jonathan,
Do you want below output ?
Q1 | Dec | Nov | Oct
yeah hashil thanks for u r response.
I need Q1 and previous 4 months like below.
Q1 | Dec| Nov| Oct | Sep
and it should change dynamically.
when it comes to Q2 it shoud display
Q2 | Mar | Fed | Jan | Dec
etc..
please be helpful in my Query.
Please make below variables in order
Current Date= CurrentDate()
Current Quarter=If(Quarter([Current Date])=1) Then "Q1" ElseIf(Quarter([Current Date])=2) Then "Q2" ElseIf(Quarter([Current Date])=3) Then "Q3" ElseIf(Quarter([Current Date])=4) Then "Q4"
Last Month=Concatenation(Left(Month(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])));3);Concatenation("-";[Current Year]))
Last Month2=Concatenation((Concatenation(Left(Month(RelativeDate(RelativeDate([Current Date];-DayNumberOfMonth([Current Date]));-DayNumberOfMonth(RelativeDate([Current Date];-DayNumberOfMonth([Current Date])))));3);"-"));If(MonthNumberOfYear([Current Date])=1 Or MonthNumberOfYear([Current Date])=2) Then (Right(FormatNumber(Year([Current Date])-1;"####");4)) Else (Right(FormatNumber(Year([Current Date]);"####");4)))
Like the same way you can make Last Month3 and Last Month4 can then concatenate them.
Harshil Joshi Thanks a lot for this very informative document. I do have question that In my current report I have to filter my current and previous fiscal year in 2 different columns (on the same report tab,) I have following dimensions from BEx query.
Our fiscal year start from July to June
Fiscal year = 2014,2015,2016
Fiscal year/period = Mar 2014 , Apr 2014 etc etc
There is no input control. Whenever user run this report it's should automatically calculate current fiscal year and previous fiscal year as well.
Could you please advice? Thanks
Hello Ali,
In your case you have to restrict the data based on current fiscal year and previous fiscal year. So you have to make variable at BEx level to restrict the data. (1) Which restrict current fiscal year and (2) which restrict previous fiscal year and then you have to make it on web I level.
My article is showing the date dimension on web I level not for data restriction.
Cheers.
Hi Harshil,
Thank you very much for your reply, Exactly I don't want data restriction/filters on BEx I need to control my fiscal year's dates.
Please let me explain; if user run this report (Current date 05/05/2016) then I need to display date from (07/01/2015 to 04/05/2016) which is my current fiscal year (July 2015 to June 2016) and at the same time I need to fetch dates for previous fiscal year (July 2014 to June 2015)
Current fiscal year result should be (07/01/2015 to 04/05/2016)
Previous fiscal year result should be (07/01/2014 to 04/05/2015)
Your help will be greatly appreciated.
Thanks
Hi Harshil,
I have seen some of the posts and appreciate your help for helping...
I have an issue, I would like to calculate Actuals for Year to Date.
The issue is I have 5 companies each company has different Financial year end..
Ex:
Company FYD
A 2
B 3
C 6
D 12
I want to calculate the actuals based upon the below FY end and the period parameter.
In the Parameter if I select 201606, for the company A the Financial Year end is 2(Feb) so it has to calculate the Actuals from March, April, May and June.
For Company B the FY end is 3(March) so it has to calculate the Actuals from April, May and June..
For Company C the FY end is 6(June ) so it has to calculate From July 2015 to June 2016.
In the same way we need to calculate for D.
Could you please help me on the above issue.
Thanks in Advance.
Raj
Hello Raj,
It is a data restriction, so that you need to do this at Universe level not at report level.
Thanks for your quick response,
Yes it is a data restriction, FYE and Period is coming from Universe.
Could you please help me on this how to proceed further.
Raj.
Thank you Harshil,
I am able to resolve the issue.
I appreciate your help.
Raj
Hi Harshil,
I have a requirement to have some Webi reports scheduled anytime and broadcast with the current Fiscal Year and the current Period (month) data.
Fiscal_Year i.e. 2017 and Period i.e. 05 are numeric objects in the Universe.
Can you provide me with a formula? And how to implement it? am not sure which is better, at report or Universe level?
Or is it simpler to just use the system date in a filter?
I would really appreciate your help. Thanks in advance.
Enrique
Hi Harshil,
Can you please help me with Last week formula.
Hi Harshil,
Amazing work. i am new on Sap and your above information is more helpful.
I need help on setting Week start from Monday at 08:00 instead of Monday 00:01 for a specific report.
Hi Harshil,
Thanks for this blog
The below is my requirement
Based on Current date can we get last 12 months month end date
Example
03/31/2021
02/28/2021
01/31/2021
12/31/2020
11/30/2020
10/31/2020
09/30/2020
08/31/2020
07/31/2020
06/30/2020
05/31/2020
04/30/2020
Please let me know
Thanks
Madhu