Prism Business Intelligence Tools - Tips and How-to's

We often receive questions from users who need to create special/custom fields. Here, I am happy to share with you the way you can convert an INT field [Seconds] into HH:MM:SS string format

First, we will need to add three custom INT fields (Hour, Min, Sec) to our table:

1. Hour Field = div([Seconds],3600)
   
   
2. Min Field  = div(Mod([Seconds],3600),60)
   
   
3. Sec Field = Mod([Seconds],60)

Finally, we will create a fourth field to contain the final HH:MM:SS string (HourRep). We do this by concatenating the three values, separated by colons (:) and we also include a '0' before any single-digit values. This is the code:

concat(
    concat(
        concat(
    -- Get hours indication depending on number of resulting digits.
    ifstring(([Hour]< 10), concat('0',ToString([Hour])),ToString([Hour])),':'),
        concat(
    -- Get minutes indication depending on number of resulting digits.
    ifstring(([Min]< 10), concat('0',ToString([Min])),ToString([Min])),':')),
    -- Get seconds indication depending on number of resulting digits.
    ifstring(([Sec]< 10), concat('0',ToString([Sec])),ToString([Sec])))

Good luck!