Date Variables in Web Intelligence

[harshil_joshi]

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