Display seconds in the format HH:MM:SS

[former_member145910]

In a report in SAC, I have measure columns that display seconds (call center metrics). These measures need to be displayed in the format HH:MM:SS format. Could anybody please let me know how this can be done in a SAP Analytics Cloud story?. The story is sourced by a data model in SAC, the datasource probably being Netezza.

Regards,

Madhu

[Sowmiya_C]

Hi Madhu Lodugu,

If you need to display your data in HH:MM:SS format ,then you data type must be assigned as Timestamp in Datamodel.

Please check,and let me know if it works.

Thanks,

Sowmiya

[DebjitSingha]

adding to Sowmiya’s your format will be comparable to UTC setup.

Thanks,

Debjit

[former_member145910]

How can this be done?. For instance, if I have a value like 6000 seconds in a field called 'avg wait time'.

I need to convert the value to '01:40:00' which is nothing but 1 hour, 40 minutes, and 0 seconds. How can this be achieved using the datatype time stamp, if so how? Please clarify.

[N1kh1l]

mlodugu

I think 6000 seconds is measure value. You will need to do the conversion by dividing the 6000 seconds first by 3600 which will give you 1.6667. Now number before decimal is the hours part in this case 1. Now take the remaining .6667 and multiply it by 3600 which will give you 2400. Divide this 2400 by 60 to get the minutes which in this case is 40. Similarly if you get decimal values for hours take the remaining and multiply it by 60 to get the seconds. I am not sure if there is a standard conversion available for this in SAC. I have worked out an example for you below.

Please note you can also do this calculation in Data wrangling also. Calculated columns for each of the below calculations. Then you can combine them together.

The other helping measures can be hidden.

Below are the formula for each measures.

Hours Calc

["NA_SAP_CEP_SALES_PLANNING":AVG WAIT TIME]/3600

Hours

INT([#Hours Calc])

Remaining Sec

([#Hours Calc]-[#Hours])*3600

Minutes Calc

[#Remaining Sec]/60

Minutes

INT([#Minutes Calc])

Seconds

([#Minutes Calc]-[#Minutes])*60

Please upvote/accept if this helps

Regards

Nikhil

[former_member145910]

Hi Nikhil,

Thank you. I was able to get the above result i.e., displaying hours, minutes, and seconds in separate columns by using floor & mod functions. The question is how do I combine the three values into one field that displays '01:40:00' if the number of seconds provided is 6000?

I have tried different ways to convert them into strings first so that I can concatenate the three measure fields but it doesn't work.

Please suggest if there is a way to change the three fields into text or if there is any other way to get the result.

Regards,

Madhu

[N1kh1l]

mlodugu

I don't think its feasible to combine them together. Try measure based dimension (calculated measure)to convert them to dimension . Then use calculated dimension to combine all three.

Nikhil