Reg: Week Definition in a report

Question

Hi all,

There is a business requirement to build a report based on the following criteria.

My report consists of year, month, week, and a measure (volume) column.

The week definition is not dependent on Gregorian or business fiscal calendar. It is just breaking up a month based on 7 days. For example, for Feb of any year, 1-7 is considered as first week, 8-14 as second week, etc.

How to build the weeks in a month and i have to build for all the 12 months in a year?

Using OBIEE 11g, Cloud instance (Don't have access to RPD). I have to build the logic in the report only.

Regards,

Ram.

User_CEU9I · Answer

Hi Gianni,

My apologies for adding one more question to this thread.

Initially these two are requirements, but initially, i was unable to achieve week definition so i asked that question. Both are resolved. Thank you so much.

Regards,

Ram.

Gianni Ceresa · Answer

First thing you are hijacking your own thread, making it a bit useless for others with similar questions as you change (add more) question in the same thread.

To keep things useful for everybody (it's a community forum) if you got an answer working for your original question close the thread marking as required. If you then have a different question open a new one (in this case your question isn't related to the week calculation at all as having a column for weeks from your DB wouldn't change the issue).

For you need I wouldn't use MAX but more do a rank on the values and filter to keep only rank = 1, the ranking is of course by week again. In this case you also have the date. If using MAX you can have the max value but as you saw no way to get the corresponding date.

User_CEU9I · Answer

Hi,

Thanks for the reply. I am working in the same way that you have suggested, it's working fine. I need the date on which max value of volume delivered in that particular week.

Scenario: For the year 2017 and January month i have taken.

columns are : Year, Month, Volume, date, week.

I need to get the max value (volume) dispatched (date) for week1, week2, week3, week 4, week5.

In the above screen shot Max values for week1 = 1,512 (1/2/2017), week2= 2,710 (1/10/2017) etc.

I need to get max value in one column and corresponding date in other column.

Week column has following formula:

CONCAT("- Shipment Period Dimensions"."Month ID", Concat(' Week ',  cast(CEILING(CAST(DAYOFMONTH(cast("- Shipment Dimensions"."Attribute Date 7" as date)) as DOUBLE) / 7.0) as char)))

and

"Max volume" column has the following formula:

max("- Shipment Facts"."Gross Volume Base" by CONCAT("- Shipment Period Dimensions"."Month ID", Concat(' Week ',  cast(CEILING(CAST(DAYOFMONTH(cast("- Shipment Dimensions"."Attribute Date 7" as date)) as DOUBLE) / 7.0) as char))))

Here i am unable to get the date on which this max volume dispatched. How to get the date?

Regards,

Ram.