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


To report this post you need to login first.

99 Comments

You must be Logged on to comment or reply to a post.

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

    (0) 
    1. Harshil Joshi 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.


      (1) 
    2. Vijay Baskar Narayanan

      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

      (0) 
      1. 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)

        (0) 
        1. Vijay Baskar Narayanan

          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

          (0) 
            1. Vijay Baskar Narayanan

              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.

              (0) 
  2. Mark Prosser

    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?

    (0) 
    1. Harshil Joshi 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.

      (0) 
    1. Harshil Joshi 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.

      (0) 
  3. mahesh bondulabo

    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

    (0) 
    1. Harshil Joshi 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.

      (0) 
  4. Archana Reddy

    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 ?

    (0) 
  5. Archana Reddy

    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    

    (0) 
    1. Harshil Joshi 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.

      (0) 
  6. Archana Reddy

    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?

    (0) 
    1. Harshil Joshi 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.

      (0) 
  7. Barry Anderson

    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?

    (0) 
    1. Harshil Joshi 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 ?

      (0) 
  8. BI DEVELOPER

    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?

    (0) 
    1. Bijal Bhatt

      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

      (0) 
  9. Archana Reddy

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

    (0) 
    1. Harshil Joshi 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…..!!!

      (0) 
    2. haresh pipariya

      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

      (0) 
  10. Ram Kishore

    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

    (0) 
    1. Harshil Joshi 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.

      (0) 
      1. Devadas Perika

        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

        (0) 
          1. Devadas Perika

            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

            (0) 
  11. Fahad Shaikh

    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.

    (0) 
    1. Fahad Shaikh

      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

      (0) 
      1. Harshil Joshi 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.

        (0) 
        1. Fahad Shaikh

          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

          (0) 
  12. Fahad Shaikh

    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…

    (0) 
  13. Fahad Shaikh

    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

    (0) 
  14. Chandan Das

    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.

    (0) 
  15. Chandan Das

    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

    (0) 
    1. 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

      (0) 
        1. 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

          (0) 
  16. 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

    (0) 
  17. Sai Kumar

    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

    (0) 
  18. Harshil Joshi 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

    (0) 
  19. Usman Tariq

    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

    (0) 
    1. Vijay Baskar Narayanan

      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

      (0) 
  20. Ben Li

    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

    (0) 
  21. jonathan n

    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…

    (0) 
      1. jonathan n

        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.

        (0) 
        1. Harshil Joshi 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.

          (0) 
  22. Ali Ali

    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


    (0) 
    1. Harshil Joshi 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.

      (0) 
      1. Ali Ali

        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

        (0) 
  23. Raj Bingi

    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

    (0) 
      1. Raj Bingi

        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.

        (0) 
  24. Enrique Rollano

    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

     

    (0) 

Leave a Reply