4 Replies Latest reply on Mar 8, 2017 10:24 AM by 1484404

    Reg: Week Definition in a report

    1484404

      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.

        • 1. Re: Reg: Week Definition in a report
          Gianni Ceresa

          CEILING(CAST(DAYOFMONTH("Time"."T00 Calendar Date") as DOUBLE) / 7.0)   (the cast is because of integer division ...)

          This gives you the week number per month, concatenate it with a reference for the month and you have your week.

          So something like 2017-03 week 1, 2017-03 week 2 etc.

          (based on your definition of week you can't have week 1 to week 52/53 for the year as your weeks don't have 7 days each)

          • 2. Re: Reg: Week Definition in a report
            1484404

            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.

            • 3. Re: Reg: Week Definition in a report
              Gianni Ceresa

              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.

              • 4. Re: Reg: Week Definition in a report
                1484404

                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.