Skip to Content
Author's profile photo Harshil Joshi

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


Assigned Tags

      101 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Arijit Das
      Arijit Das

      I did not go through the entire doc. But I am confused at point #2.

      (2) Current Year =If(Month([Current Date]) InList("January")) Then(FormatNumber(Year([Current Date])-1;"###")) Else (FormatNumber(Year([Current Date]);"###"))

      Why for January, you are assigning the previous year to variable current year?

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.


      Author's profile photo Arijit Das
      Arijit Das

      OK

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Arijit Das
      Arijit Das

      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)

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Arijit Das
      Arijit Das

      v_FirstOrSecondHalfOfYear = If(MonthNumberOfYear([YourDateObject])<7;1;2)

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      Please send me some sample doc to review and to test locally.

      Author's profile photo Vijay Chandra.R
      Vijay Chandra.R

      Great work Harshil

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thank You very much Vijay.

      Author's profile photo Former Member
      Former Member

      Appreciate your efforts. Thanks for sharing.

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thanks Bijal.

      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      Good job harshil..I shall use them soon..Bookmarked!

      Regards,

      AL

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thanks Anshu.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Final point, why use Inlist("January") when you can use ="January" instead?

      Yes you can.

      Thanks a lot for your consideration.

      Author's profile photo Former Member
      Former Member

      Nice, keep it up. 🙂

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thank You Giri Prasad......much appreciated...

      Author's profile photo Former Member
      Former Member

      Hi Harshil,

      Good information. Bookmarked and Liked. But what about date conversion and date difference ?

      Can you please add the same in it ?

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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/

      Author's profile photo Former Member
      Former Member

      Thanks a lot! Great Job

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thank you Sreedhar.

      Author's profile photo Nirav Gandhi
      Nirav Gandhi

      Good article ...Harshil

      Can we find weekday between two dates?

      Like no of days excluding sat and sunday between this two dates.

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Author's profile photo Nirav Gandhi
      Nirav Gandhi

      Thanx harshil...

      Author's profile photo Former Member
      Former Member

      Good one...Thank you for sharing useful info.

      Chandrakanth

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thank You Chandrakanth.

      Author's profile photo Wasem Hassan
      Wasem Hassan

      Hi Harshil

      its too good doc.

      Thanks for sharing.. 🙂 🙂

      Regards

      Waseem

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thank you wasem hassan. I appreciate your words. Thanks a lot.

      Author's profile photo William MARCY
      William MARCY

      Great Webi trick !

      You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

      William

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      Hi Harshil Joshi,

      Thanks for your early reply, Ur clear explanation is superb I got it now.

      Regards,

      Mahesh

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thank You. I will update my document which will cover formula to show 2013-2014. As this is asked by many so.

      Author's profile photo Former Member
      Former Member

      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 ?

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Hi Archana,

      You have hire_date in SQL table, but where anniversary_date is stored ?

      Author's profile photo Former Member
      Former Member

      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    

      Author's profile photo Former Member
      Former Member

      BTW I dont have access to universe. So I need to create variables formulas at webi report level

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Capture.PNG

      Now make a filter as below

      Capture.PNG

      Now you will get list of employees who have anniversary in current month (say March)

      Capture.PNG

      Hope it helps.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Former Member
      Former Member

      Iam trying to use daysbetween but its giving me error ...

      I am getting unrecognized input

      =DaysBetween([Hire Date],CurrentDate())/365

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      Hello Harshil,

      Very useful document... Thanks for sharing...

      Regards

      Subbarao M

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thank You Very Much.

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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 ?

      Author's profile photo Sastry Duvvuri
      Sastry Duvvuri

      Hi Harshil,

      Very helpful document, I feel like every one bookmark this for further reference.

      Good Job !!

      -Sastry

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Thank You Sastry.....

      Author's profile photo Former Member
      Former Member

      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?

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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());"####")

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Hi Archana,

      You want to extract date & month from your date JoinDate right?

      You can use Left([joindate];4) and then format date.....!!!

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo D DURGA THRINAD
      D DURGA THRINAD

      thanks for the info ,its a great learning 🙂 from you.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Devadas,

      Do you want this kind of structure ? ?temp.PNG

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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)Help.jpg

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Hi Fahad,

      The format of two date should be same as "date". Please check.

      If not then use FormateDate Function for the same.

      Author's profile photo Former Member
      Former Member

      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)

      Help.jpg

      Author's profile photo Former Member
      Former Member

      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...

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Rishabh Singhania
      Rishabh Singhania

      very hlpfull...thanks for sharing

      Regards,

      Rishabh

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      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

      tcd.PNG

      please do the needy help

      Regards,

      Tulasi

      Author's profile photo Rishabh Singhania
      Rishabh Singhania

      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

      Author's profile photo Former Member
      Former Member

      Hi Rishabh,

      We are using Bex, On top of that we have webi report, could you help on this

      Thanks

      Author's profile photo Rishabh Singhania
      Rishabh Singhania

      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

      Author's profile photo Former Member
      Former Member

      Thanks Rishabh,

      here we have restriction, i.e on ly do changes in Webi level only.

      Author's profile photo raghu nath
      raghu nath

      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

      Author's profile photo Former Member
      Former Member

      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!!Untitled.png

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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

      test.PNG

      Further you can do by if else statement

      Author's profile photo Former Member
      Former Member

      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 datewebi.PNG

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Very good doc..

      Author's profile photo Former Member
      Former Member

      Its really very useful documents.

      All level of Developers can be easily understand your documents.

      Keep going

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Hi experts Appreciate response on same

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Hi Ben Li,

      Hope you have got your solution.

      Author's profile photo Former Member
      Former Member

      No Harshil, I havent received solution so far. Appreciate any input on this

      Author's profile photo Former Member
      Former Member

      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...

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Hi Jonathan,

      Do you want below output ?

      Q1 | Dec | Nov | Oct

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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


      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      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.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Harshil Joshi
      Harshil Joshi
      Blog Post Author

      Hello Raj,

      It is a data restriction, so that you need to do this at Universe level not at report level.

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member

      Thank you Harshil,

      I am able to resolve the issue.

      I appreciate your help.

      Raj

      Author's profile photo Former Member
      Former Member

      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

       

      Author's profile photo Kaushal Pandya
      Kaushal Pandya

      Hi Harshil,

       

      Can you please help me with Last week formula.

      Author's profile photo MM Gaima
      MM Gaima

      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.

      Author's profile photo Madhu obireddy
      Madhu obireddy

      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