4

I need to create a report that shows the Case high traffic trends for each hour of the day for the current year. Basically, the customer wants to see a report for the current year of cases opened, grouped by the hour in which it was opened. So then they can see there busiest times of day, on average for the year.

For instance, if the case was opened at 11:30 am, then it would fall in the 11:00 -12:00 group.

I have a few ideas of how to accomplish this, I just want to make sure I am going about it the correct way.

Possibilities include

  1. Create a report and/or dashboard purely in Apex and VF. Seems fairly straight forward to create, just want to make sure I need to go fully custom before I decide to go that route.

  2. Use a formula field that looks at the Date/Time opened field and pulls just the time portion and using a CASE statement populates the appropriate time interval to the field. Use standard reporting on that field.

  3. Use some scheduled/batch apex or a trigger to populate the time interval field using the Date/Time Opened field, and then use standard reporting on that field.

I am fairly confident that any one of these ways would work, I just want to make sure I'm doing it in the most efficient way and I am not reinventing the wheel. So any thoughts and or tips from people that have implemented something similar would be much appreciated.

  • I am looking to do exactly this as well, but can't follow what you are doing. I am a SF noobie, so I kind of need a step by step as to how to do this. Would you be so kind as to help me out? Thanks! Chris – user5276 Jan 13 '14 at 22:06
7

I was able to accomplish this using just a couple of formula fields. I likely could have done it with just 1 formula field, but for readability sake, I split it into 2 fields. They are both formula fields of type text.

'Case Opened Time' Field Criteria

TEXT(
     CASE(
          VALUE(LEFT(RIGHT(text(CreatedDate),FIND(" ", TEXT( CreatedDate ))-2),2)), 
          00,19, 
          01,20, 
          02,21, 
          03,22, 
          04,23, 
          05,00, 
          06,01, 
          07,02, 
          08,03, 
          09,04, 
          10,05, 
          11,06, 
          12,07, 
          13,08, 
          14,09, 
          15,10, 
          16,11, 
          17,12, 
          18,13, 
          19,14, 
          20,15, 
          21,16, 
          22,17, 
          23,18, 
          00
      )
 )

NOTE: This is set up to convert the time to EST. If you do not want to convert to EST and leave it in GMT, then you would only need the formula to read

value(right(left(text(CreatedDate),find(":",Text(CreatedDate))-1),2))

This returns just the number of the hour. For example 1:35 pm -> 13, 8:45 pm > 20, 3:15 am -> 3.

So then I created another formula field to make this more readable for the reports. It really just put the time into military format and created a range for that time.

'Case Opened Time (Formatted)' field Criteria

IF( 
     LEN( Case_Opened_Time__c ) = 1, 
          '0' + Case_Opened_Time__c + ':00 - 0' + Case_Opened_Time__c + ':59',
          Case_Opened_Time__c + ':00 - ' + Case_Opened_Time__c + ':59'
  )

All this does is return the hour range that the case was created in. For example 1:35 pm -> 13:00 - 13:59, 8:45 pm > 20:00 - 20:59, 3:15 am -> 03:00 - 03:59

Now that I had the formula field that gave me this time range, I was able to simply set up a standard summary report that showed the number of cases for the year, by the hour of day, that the case was opened. Here is a shot of the chart for the dashboard

enter image description here

Hopefully this helps someone out that wants to do something similar to this.

|improve this answer|||||
  • This is awesome. Thanks! I wonder if there is a similar solution to group dates by the week they fall in. – Richard N Mar 19 '14 at 16:11
  • 1
    There is simpler way to return hour from date/time field help.salesforce.com/articleView?id=000005369&type=1 MID( TEXT( CreatedDate - 5/24 ), 12, 2) ) – sfdc Mar 16 '17 at 18:02
3

I vote for option #2: using a formula field to extract the hour. I prefer this method because:

  1. It is completely declarative
  2. It is the simplest method
|improve this answer|||||
  • I agree this is likely the way to go. I'm going to give it a shot and I will post back what I come up with once I get working – Chris Duncombe Sep 25 '13 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.