Trying to aggregate a measure by HOUR over multiple DAYS

Question

I am trying to do some trending over hours during specific days of the week. By this I mean I might want to see how many calls came in (BY HOUR) over the past 3 Days that were the same day name as yesterday's day name (for example, today is Wednesday so I want to see how many calls came in over the past 3 Tuesdays...by HOUR).

Here is what the raw data might be like:

CallDateTimeCallCount7/25/2016 10:00 AM47/25/2016 10:05 AM37/25/2016 10:30 AM27/25/2016 10:00 AM37/25/2016 11:05 AM27/25/2016 11:45 AM37/25/2016 12:05 PM28/1/2016 10:05 AM58/1/2016 10:25 AM28/1/2016 11:30 AM18/1/2016 12:15 PM48/8/2016 10:15 AM38/8/2016 11:45 AM38/8/2016 11:55 AM48/8/2016 12:35 PM2

Then I would like for the table of my report to be something like: (I will be eventually put this into a line graph)

CallTime
CallCount
10:00 AM2211:00 AM1312:00 PM8

I believe I already have my filters correctly put into the report: ('yesterday' is a repository variable that we use to always give user yesterday's date)

--"CALL_CENTER"."CALL_START_DATETIME" BETWEEN  VALUEOF("yesterday")-14 AND  VALUEOF("yesterday")

--DAYOFWEEK("CALL_CENTER"."CALL_START_DATETIME") =  DAYOFWEEK(VALUEOF("yesterday"))

Where I run into my problem is...I can't seem to figure out how to get the CallCount to aggregate by HOUR but NOT by DATE.

The formula I use for the hour in CallTime is as follows:

EVALUATE('trunc(%1,%2)' as TimeStamp,"CALL_CENTER"."CALL_START_DATETIME" ,'HH')

I then set a custom format for CallTime to be:

h:mm tt

Cosmetically this gives me what I want for CallTime, but it obviously still has the DATE in it (even though I'm not showing it). So my report ends up repeating Hours and giving CallCount for each of these Date/Times.

I need it to aggregate up for each hour.

Is this possible? And if so, could anyone help steer me in the direction I'd need to go in order to achieve this?

Any help is appreciated!

Chris Arnold · Answer

Thank you Gianna.

I am now just using the HOUR function as such:

CAST(hour("CALL_CENTER"."CALL_START_DATETIME") as CHAR)

In that same column formula I also use a case statement to change the hour into the format I wish. For example:

CASE

WHEN CAST(hour("CALL_CENTER"."CALL_START_DATETIME") as CHAR) = '0' then '12:00 AM'

WHEN CAST(hour("CALL_CENTER"."CALL_START_DATETIME") as CHAR) = '1' then '1:00 AM'

and so on...

I then just make a separate column that I can use for sorting:

hour("CALL_CENTER"."CALL_START_DATETIME")

So...just as you suggested this is giving me what I desire. Thank you!

Gianni Ceresa · Answer

Hi,

Did you try to just get your time as a varchar? Because to achieve what you look for you must get rid of the date component and keep only the time (or transform the date to always be the same date), so in OBIEE you have the HOUR function returning you a number between 0 and 23 and you can then format it looks like the hour you look for.

In this way your multiples days will aggregate together by hour as you don't have date component anymore (actually it's not a date at all anymore).