Arranging date ranges — Oracle Analytics

Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Arranging date ranges

Received Response
11
Views
2
Comments
User_CEU9I
User_CEU9I Rank 5 - Community Champion

Hi all,

I have a report requirement in the following way.

pastedImage_0.png

and i have build the report in the following way. Please see the crieteria.

pastedImage_1.png

I have calculated the date range like this

for 12AM- 8 AM  the formula is CASE WHEN HOUR("Date 5") >= 0 AND HOUR("Date 5") < 8 THEN '12AM-8AM' END

similarly for others i have calculated in the same manner.

I am unable to do the following things.

1. How to put hours range (12AM-8AM,8AM-4PM,4PM-12AM) in the above the report requirement order i.e one below the other?

2. How to calculate <1hr, 1-2 hrs,2-3 hrs,>3 hrs columns as shown in the above report requirement?

I have a limitation to access rpd and database, so i have to do all at the report level only. I am using OBIEE 11g.

Request you please help me.

Regards,

Ram

Answers

  • Hi, for 1) if you really want to be sure about your order create a second column with the same CASE WHEN but returning a value (number) you use for sorting. You then make this column as hidden and add it just before the real column with the 12am-8am etc. values. In that way sorting on that numeric hidden column your sorting will always be fine.

    For 2) depends what does these things means for you ... It's your business which is supposed to tell you how to calculate things.

    We can of course give you random answers on the business rules you can apply to your column but I guess your business users will probably disagree...

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    #2 - use of information drives physical design ... why not have the values already calculated and stored at the intersection of timeofday and duration -- these should be dimensions which drive the grain of your fact table