obiee 11g Get first day of week as monday based on a specific date

BenFreez

Hi all,

I'm looking a solution to deploy in the RPD a way to retrieve from a specific date the First Day of the Week based on this date.

My week is starting from Monday to Sunday

Thanks in advance for your help.

Ben

Joel Acha

You could do this in the ETL by extending your time dimension. By adding an additional column which links back to the 1st day of the week for each day recorded in your time dimension.

Joel Acha

Alternatively, you could implement something like this in the rpd:

TIMESTAMPADD(SQL_TSI_DAY, CASE WHEN DAYOFWEEK("Time"."T00 Calendar Date") <> 1 THEN -1*(DAYOFWEEK("Time"."T00 Calendar Date")-2) ELSE -6 END, "Time"."T00 Calendar Date")

This formula assumes that Sunday is the 1st day of the week and Monday is the 2nd day of the week (in bold in formula).

BenFreez

Thanks a lot for the precious formula.

In my case my week is starting on Monday instead of Sunday. I need to alter your formula to take care of that fact

Ben

Joel Acha

If that sorts out your issue, please close the thread for others who may have this issue in future.

BenFreez

Thanks a lot for your help.