Minutes and Seconds - Salesforce Analytics Cloud

Recently I came across a requirement to have a metric / measure that was sourced in seconds that then need to be converted into minutes and seconds and be displayed on a dashboard. Unfortunately plotting this in this format isn't avilable to be plotted in a single widget or graph.

To do this in the current version of Salesforce Analytics we need to create multiple steps and calcuations to get to our required outcome. This will become easier in the lastest release of Analytics Cloud due the the following added functions :

  • Ceil
  • Round

Further information on these Functions can be found HERE

The first step we need to add into the steps section in the dashboard JSON is the pigql query for the initial measure that will be used for the calulation represented in minutes and seconds ( mm:ss ). The measure below is a simple 1 column calculation for the average of the HoldTime measure.

The next step is the conversion step. We need to take the original Hold Time average calculation step above and convert the calculation into a simple integer. Although this will become easier in the Winter 16 release, currently we have to build a dataset specifically for this purpose - you can find that dataset HERE: However it is essentially a column of letters and a column of consecutive numbers from 0 to 40,000. Once it has been built and uploaded add the name of the dataset into the code below where \"integer\" is.

After the conversion step we then move to the format step. In this step we take the Converted Hold time metric and build out 2 measures one measure for seconds and one measure for minutes.

Once we have our steps from the initial calculation to the formatting step and added this into the dashboard JSON, We now add the widget components. These can be seen below they are essentially the same however point to the different metrics being maxseconds and maxminutes.

The end result is that a measure that original was 394 seconds now will show as 6 34 , split between two number widgets. These can then be placed next to each other to show 6:34. This is how we would currently take a data set with measure in seconds and using the number widget convert into a displayable minutes and seconds.

 
 
 

One of the biggest issues with the above calculation steps is that there maybe data that doesnt exist yet when trying to pair the average Hold TIme to some specific dates. For example the calulation paired with a Static Date Step where they attribbutes could range from yesterday to Last year. In this scenario there maybe no data from yesterday dependant on the data load or what other filters maybe applied to the calulations for examaple type of call that is being held. In the above calculation we have included some extra items in the pigql query to handle this for us:

s = foreach q generate 0 as 'avg_HoldTime'; 
q = filter q by date('Date_Year','Date_Month', 'Date_Day') in {{no_quote(value(field(selection(DateRange_Step),'saql_q')))}}; 
q = group q by all; 
q = foreach q generate avg('HoldTime') as 'avg_HoldTime';
r = union q,s; 
r = group r by all; 
r = foreach r generate sum('avg_HoldTime') as 'avg_HoldTime';",

In the above example we have taken essentially union'd two tables one with the original data and the second with a 0 generated for the missing values. This allows analytics cloud to sum 0 where there is no data in the current query and represent that in the dashboard.