My point is a crucial one.

Keys are of paramount importance in OBIEE.

In the AL_PERIOD_SCH_SHIP_DATE I would expect (in one row for todays date as an illustrative example); -

YEAR_ID 2017

QUARTER_ID 201704

MONTH_ID 201711

WEEK_ID (I see this is not present in your table, sound of alarm bells here!!! - your week probably looks like 47 not 201747 - i.e. it is not a key which would let me find ONLY the week in question, BECAUSE IT LACKS SUFFICIENT DETAIL TO DO SO - excuse the capitals!)

So, I would suggest in your business model logical table you need to create a concatenation of Year_ID and Week to make a Week_Id which can then validly be used as the key in the time dimension and which will work in the AGO function.

Note - as a function this will not be the most performant way to do it, the best way is to go back to your Data Warehouse developers who populate the table and ask them to create a unique week id in the physical table, and then model it through to use it in the appropriate place.

For further explanation on the concepts see this => https://gerardnico.com/wiki/dat/obiee/obis/time_dimension

Sure, hope you get there.

Kindly also mark whatever helped or turned out to be correct for the benefit of those who follow.

Robert.