/* */


Dec 30 2012

Dates from the Past Week

Category: Business ObjectsIlyas @ 10:07 pm

How do I get the date for any day within the past week?

Scenario:

My report run’s every day but the data coming into the report is only for Wednesday’s. There can be scenario’s where the data might be missing for the last Wednesday and in this case I needed to show the date for the last Wednesday. So whenever the data is missing for the last Wednesday, my sales date will be less than Wednesday (as only Wednesday’s get into the report). Now how do I show what date was it on Wednesday?

Solution:

Step 1:

Say today is Friday and the day number of Friday is 5 and I need to find out what date was it on Wednesday and the day number of Wednesday is 3. So 5 – 3 = 2 is the number of days that I need to go back from today. And this can be done using the RelativeDate function but how do I calculate the day’s difference?

Let me put the logic together to get the date of Last Wednesday,

Var_Last_Wed = FormatDate(RelativeDate(CurrentDate();-If(DayNumberOfWeek(CurrentDate())=1;5;If(DayNumberOfWeek(CurrentDate())=2;6;DayNumberOfWeek(CurrentDate())-3)));”MM/dd/yyyy”)

The second half of the logic from the If Statement would give me the day’s difference and using the relative date I get the date of the desired day

I have hardcoded to go back to 5 day’s if today is Monday and 6 day’s if today is Tuesday, since Monday and Tuesday has the day number 1 & 2, and since they come before 3 (i.e Wednesday), the difference would give me incorrect results with this logic.

Step 2:

Using the above variable, create a variable that would throw the above date when max of Sales Date is less than the last Wednesday’s date.

Var_Show_Date =If (MAX([SALES DATE]) <[Var_Last_Wed];”Missing Data for” +[Var_Last_Wed]; “Full Data Set”)

 

The above logic can be tweaked to get date for any other day within the past 7 days;

var_Last_Mon=FormatDate(RelativeDate(CurrentDate();-(DayNumberOfWeek(CurrentDate())-1));”MM/dd/yyyy”)

var_Last_Tues=FormatDate(RelativeDate(CurrentDate();-If(DayNumberOfWeek(CurrentDate())=1;6;DayNumberOfWeek(CurrentDate())-2));”MM/dd/yyyy”)

var_Last_Thurs=FormatDate(RelativeDate(CurrentDate();-If(DayNumberOfWeek(CurrentDate())=1;4;If(DayNumberOfWeek(CurrentDate())=2;5;If(DayNumberOfWeek(CurrentDate())=3;6;DayNumberOfWeek(CurrentDate())-4))));”MM/dd/yyyy”)

var_Last_Fri=FormatDate(RelativeDate(CurrentDate();-If(DayNumberOfWeek(CurrentDate())=1;3;If(DayNumberOfWeek(CurrentDate())=2;4;If(DayNumberOfWeek(CurrentDate())=3;5;If(DayNumberOfWeek(CurrentDate())=4;6;DayNumberOfWeek(CurrentDate())-5)))));”MM/dd/yyyy”)

var_Last_Sat =FormatDate(RelativeDate(CurrentDate();-If(DayNumberOfWeek(CurrentDate())=1;2;If(DayNumberOfWeek(CurrentDate())=2;3;If(DayNumberOfWeek(CurrentDate())=3;4;If(DayNumberOfWeek(CurrentDate())=4;5;If(DayNumberOfWeek(CurrentDate())=5;6;DayNumberOfWeek(CurrentDate())-6))))));”MM/dd/yyyy”)

var_Last_Sun =FormatDate(RelativeDate(CurrentDate();-(DayNumberOfWeek(CurrentDate())));”MM/dd/yyyy”)

 

Tags: ,

Leave a Reply

You must be logged in to post a comment. Login now.