/* */

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”)