Logo

Tag Archive for activities

Convert Decimal Time Into Hours and Minutes – HH:MM – Wave Einstein Analytics

Salesforce Quick Tips

Thought I would write about this Analytics Quick Tip as I have found zero information related to this online and I would imagine it would be a pretty common use case.

The Problem With Decimal Hours

Often you have ‘duration’ type measures stored as ‘number of seconds.’  Think of the duration of a service ticket from one stage to the next, or the duration of a sales call.  Many times you want to aggregate all of these together for a particular user or particular day.  Once you’ve aggregated them together, you can easily convert these into decimal hours by dividing by 60 to arrive at # of minutes and 60 again to arrive at # of hours (divide by 3600 once to shortcut this step).  The trouble is then you have a confusing metric of decimal hours.  3.45 hours does not equal 3 hours and 45 minutes.  It is closer to 3 hours and 30 minutes!  4.87 does not equal 4 hours and 87 minutes!  No matter how many times you try and educate your users on decimal hours it is confusing for them to discuss with their teams and properly rank and compare them to each other.

A SAQL Solution For Converting Decimal Hours to HH:MM

I think it’s helpful to break each step down into its component part before we get into the code of how we are going to accomplish this.

We want to take 4.87 and convert to 4:52.

  1. First we want to take out the # preceding the decimal point as it is already a valid # of hours. (4)
  2. Then we want to add a colon to separate hours from minutes. (4:)
  3. Then we want to take the remaining number post decimal point and multiply by 60 (this number will be formatted to only 2 digits and placed after the colon). (4:52 – we arrive at this by taking the entire 4.87 and subtracting the truncated number of 4 which leaves .87 and then we multiply that by 60)

Simple corresponding example below without formatting and where ‘decimalhour’ is already in decimal hours.  You would just replace ‘decimalhours’ with your duration measure and divide by 3600 if converting from seconds.

1. trunc(sum('decimalhours'))
2. + ":" +
3. sum('decimalhours') - trunc(sum('decimalhours')) * 60

Here is an example of the full saql w/ formatting.

 number_to_string(trunc(sum('decimalhours')),"#") + ":" + number_to_string(((sum('decimalhours')) - (trunc(sum('decimalhours')))) * 60,"0#") as 'HH:MM'

Now we have a very easy to read table in hours and minutes!

Owner Total Time
Bruce Kennedy 4:36
Catherine Brown 4:02
Chris Riley 3:55
Dennis Howard 3:29

The only issue I’ve found is when someone is right on the hour, say 4:00 and it only displays a single zero. I’ve solved for this with a case statement as in all of my use cases we are 5 hours and below so I only need 5 case statements.  This is very easy to do.  I had first tried replace but it would then replace all scenarios where there is a zero after the colon.  I’m sure there is a more elegant solution but I have not had the chance to spend very much time on this piece and the case statement solved my particular situation.

Did this solution help you?  Do you have another method to solve the same problem?  Let me know in the comments.  I would love to hear from you!

How to Close or Complete Event Activities + Salesforce Bug!!

Salesforce Events – Closed or Open?

Do you know what determines whether a Salesforce Event Activity displays in the “Open Activities” related list or the “Activity History” related list?  Let’s start with an event activity with a start/end date in the past:

Event Details

This record displays on the Account detail page within the “Activity History” related list, not the “Open Activities” section.

account activity history related list

What about when you are selecting to view Open or Completed Activities within a Salesforce Report?

report show open or completed activities

 

Events Are Automatically Completed/Closed

Salesforce documentation indicates “All events will automatically be moved to the Activity History the day following the scheduled event date.”  In other words, unlike tasks where they are manually marked “complete” – events are automatically completed after the scheduled date has past.

I found this very interesting because I’ve seen events appear “completed” on the same day as the scheduled start/end date.

A Warning About Utilizing Report Data

Within Salesforce Reports, there is a field labeled “Closed.”  This field indicates whether the task or event record has been completed.  This isn’t an actual field on the record and cannot be found within the database utilizing a SOQL query.

event soql db details no closed field

The issue with Events is that the same exact “Closed” field can display different results depending on whether it is a standard or custom report type.

The same record above that displays in the “Completed Activities” related list displays as “Closed” = True in the standard activity report type:

Standard Report Type Closed Event

However, when using a custom report type the same record displays as “Closed” = False.

Custom Report Type Open Event

Come on Salesforce!  This can’t be by design, can it?!  I can only assume that because it’s really a ‘faux field’ and the report is determining whether the event is closed or not that this logic was only applied to the standard report types.

This can be dangerous particularly if you were using this field in a CRT as filter criteria or using this field to aggregate – sum/avg/etc across all activities.

What are your thoughts or issues with Events?  Have you created a custom field to let users manually indicate whether they have completed the activity?  There are numerous ideas on the Success Community regarding the way Events are marked complete.  Here’s one.  Please let me know your thoughts in the comments or on twitter @SFDC_r – I would love to hear from you!