How to display dates based on Week Ending Date

[rajua99]

Hello,

In my report i have weekending date and day of week and i want to display the Date for the Day of week based on week ending date.

For Example if the Week ending date is September 9th (Saturday) and when the Day of week is 1 i want to display Date as September 3rd (Sunday). For Day of week 2 September 4th(Monday) etc...

I need help in creating the above formula in Report Studio.

Thanks in advance

[New_Guy]

Hi,
Try to create a data item with the below expression, syntax might not be 100% right. Let us know if this helped out.

case
when [Day of week] = 1 then _add_days([week ending date],-6)
when [Day of week] = 2 then _add_days([week ending date],-5)
when [Day of week] = 3 then _add_days([week ending date],-4)
when [Day of week] = 4 then _add_days([week ending date],-3)
else
[Day of week] = 5 then _add_days([week ending date],-2)
end

Good luck
New guy

[rajua99]

Thanks you. Here is the final formula that i used to make it working..

CASE
when [Day of Week] = 1 then _add_days([Week Ending Date],-6)
when [Day of Week] = 2 then _add_days([Week Ending Date],-5)
when [Day of Week] = 3 then _add_days([Week Ending Date],-4)
when [Day of Week] = 4 then _add_days([Week Ending Date],-3)
when [Day of Week] = 5 then _add_days([Week Ending Date],-2)
when [Day of Week] = 6 then _add_days([Week Ending Date],-1)
else
[Week Ending Date]
END

[BigChris]

Couldn't you just use something like:

[Week Ending Date] - 7 + [Day of Week]

[Invisi]

rajua, is the end of week date in your Date dimension? Consider that when this date is important for your users, you can add it to your Date dimension, making the work to be done in the visualisation tool (Cognos) simpler.