Fiscal Week Formula

Question

Hi All,

I am very new to OBIEE 12c

I have a fiscal date metric that displays like 2017WEEK01 for the first fiscal week of February.

Is there a way to us SQL to dynamically know which fiscal week to use based on the date that the report is being run?  I don't want to have to use a prompt each time for someone to have to select fiscal week.

Thanks!

[Deleted User] · Answer

If you have this modeled into your time dimension (since "Fiscal Week" is no absolute concept like the month of "January") then it simply becomes an attribute on the one hand and also an alternative roll-up hierarchy type within your time dimension and you can drill total -> Fiscal Year -> Fiscal Quarter -> Fiscal Week -> Day for example.

MUCH better approach than any SSQL statement because it's data and not code.

Gianni Ceresa · Answer

Hi,

You generally set a variable (repository variable refreshing every few hours or even just once per day, depending on the different timezones you must cover).

The query sourcing the variable will use your own time dimension to get the value of the fiscal week column based on the current date.

Or just using the same logic to filter your week based on the result of a dimensional only analysis filtering current date and return fiscal week.