locked
How to show a column containing seconds duration in integer format as HH:MM:SS? RRS feed

  • Question

  • Folks,

    I need some advice on the best way to show seconds in the HH:MM:SS format in a PowerPivot model (it will be imported as SSAS Tabular later).

    I have been reading some good stuff around the Internet, but I have a couple of issues:

    • I know we have the time dataset in SQL but the fact is that PowerPivot and SSAS Tabular does not have time-only fields. So even if I had a field like 21:12:00, this field would become effectively 30/12/1899 (or another date), followed by the time 21:12:00. Even if I format the field to not show the date but only the time, I can foresee issues with this. I really need only the time.

    • Another issue with the time dataset is that it represents a time of the day. What I really want is a duration, not the time of the day. So I would like to allow users to specify parameters concerning the duration such as less than 5 minutes, more than 30 minutes and so forth and not necessarily the time of the day. There is also the issue that a time field could only go as far as 24 hours -- but it is improbable that we will ever have such duration.

    • So I am torn between what I can do concerning the formating of the field and the parametrisation that users could do.


    Any suggestions? Thank you all for your help!

    Best regards,
    P.



    • Edited by pmdci Friday, November 21, 2014 4:05 PM
    Friday, November 21, 2014 4:04 PM

Answers

  • Power View view filters will fill your need. Just put [DurationInMinutes] or [DurationInSeconds] on the page as a view filter and the users will have a slider bar of durations.

    On the tables/charts, put the [DurationFormatted] on as your label.

    If you need to find, for example an average call duration, you'll do something like the following:

    AverageDuration:=
    AVG( Table[DurationInSeconds] )
    
    AverageFormatted:=
    RIGHT(
        "0" & FORMAT( INT( [AverageDuration] / 3600 ), 0 ) 
        , 2
    )
    & ":"
    & RIGHT(
        "0"
            & FORMAT( 
                INT( MOD( [AverageDuration] / 60, 60 ) )
                , 0
            )
        , 2
    )
    & ":" 
    & RIGHT( 
        "0" & FORMAT( MOD( [AverageDuration], 60 ), 0 )
        , 2
    )

    This will start to get cumbersome. You could implement a disconnected slicer to put different measures into that formatted measure, but even still it will be hairy, and that string processing is not desirable in my mind.

    I would argue that displaying these things as total minutes (with or without decimals displayed) is your best bet, especially since you claim most will be under an hour. Most people do not have a problem interpreting 30.5 minutes, for example.


    Edit for quote marks


    • Proposed as answer by Michael Amadi Tuesday, November 25, 2014 9:13 AM
    • Marked as answer by pmdci Tuesday, November 25, 2014 9:23 AM
    • Edited by greggyb Saturday, December 12, 2015 7:48 PM
    Monday, November 24, 2014 3:19 PM

All replies

  • One thing you might consider is an integer field [DurationInSeconds], and write a measure that returns a properly formatted string in HH:MM:SS format. You could then do your arithmetic on the integers and display whatever you want.

    An alternative, that may be easier or harder depending on your exact implementation, would be three fields [DurationHours], [DurationMinutes], [DurationSeconds], such that a duration of 02:58:23 would be represented by [DurationHours] = 2, [DurationMinutes] = 58, [DurationSeconds] = 23. This would make your formatting a bit easier, but it could make some of your calculations more difficult (testing 3 fields instead of one).

    Another alternative is to store your duration in two formats, [DurationInSeconds] for math and [DurationFormatted] as a string permanently formatted in HH:MM:SS format.

    Without knowing your specific model and final report display, I wouldn't feel comfortable giving specific implementations, because these could vary by a great deal.

    If you have a sample you can post and a mock-up of the final output expected, I or someone else could put together a few good examples to get you started.

    Friday, November 21, 2014 4:16 PM
  • Hi Greg,

    • The data is coming from a DSS system and it is, in fact, DuractionInSeconds.
    • The report format is simple: HH:MM:SS.
    • Technically we could have a duration that runs for 72 hours but that is simply unrealistic (these are durations of calls).
    • I would like to give users the ability to parametrise reports specifying less than more than, etc.
    • These reports will be presented in PowerView.

    Regards,

    P.

    Friday, November 21, 2014 4:30 PM
  • If you don't need to write measures that perform operations on these durations, then it should be pretty simple.

    I'd probably make a few extra fields to support this:

    DurationInMinutes=
    [DurationInSeconds] / 60
    
    DurationFormatted=
    RIGHT( 
        '0' & FORMAT( INT( [DurationInSeconds] / 3600 ), 0 ) 
        , 2
    )
    & ":" 
    & RIGHT(
        '0'
            & FORMAT( 
                INT( MOD( [DurationInMinutes], 60 ) )
                , 0
            )
        , 2
    )
    & ":" 
    & RIGHT( 
        '0' & FORMAT( MOD( [DurationInSeconds], 60 ), 0 )
        , 2
    )
    

    You might want a [DurationInHours] following the format of [DurationInMinutes].

    I would then implement a view filter in Power View using [DurationInMinutes], and I'd use the [DurationFormatted] as your display field.

    If you need to do any aggregation (Avg call length, Total call length for a time period, or anything else), you'll have to calculate that based on your [DurationInSeconds], then to format it, you'll have to pass that calculation through a measure that looks a lot like the column definition above for [DurationFormatted].

    Let me know if you have any questions on this.

    Friday, November 21, 2014 5:32 PM
  • Hi Greg,

    Thank you for that.

    I did mention though that I would have to perform operations on these durations, right? Such as filtering rows shorter than X seconds but grater than Y seconds.

    As for measures, at first I think I will not need those. Do you have any thoughts on these? What sort of possible measures do you envisage? Do you mean as examples the aggregations you have mentioned?

    Regards,

    P.

    Saturday, November 22, 2014 8:15 AM
  • Power View view filters will fill your need. Just put [DurationInMinutes] or [DurationInSeconds] on the page as a view filter and the users will have a slider bar of durations.

    On the tables/charts, put the [DurationFormatted] on as your label.

    If you need to find, for example an average call duration, you'll do something like the following:

    AverageDuration:=
    AVG( Table[DurationInSeconds] )
    
    AverageFormatted:=
    RIGHT(
        "0" & FORMAT( INT( [AverageDuration] / 3600 ), 0 ) 
        , 2
    )
    & ":"
    & RIGHT(
        "0"
            & FORMAT( 
                INT( MOD( [AverageDuration] / 60, 60 ) )
                , 0
            )
        , 2
    )
    & ":" 
    & RIGHT( 
        "0" & FORMAT( MOD( [AverageDuration], 60 ), 0 )
        , 2
    )

    This will start to get cumbersome. You could implement a disconnected slicer to put different measures into that formatted measure, but even still it will be hairy, and that string processing is not desirable in my mind.

    I would argue that displaying these things as total minutes (with or without decimals displayed) is your best bet, especially since you claim most will be under an hour. Most people do not have a problem interpreting 30.5 minutes, for example.


    Edit for quote marks


    • Proposed as answer by Michael Amadi Tuesday, November 25, 2014 9:13 AM
    • Marked as answer by pmdci Tuesday, November 25, 2014 9:23 AM
    • Edited by greggyb Saturday, December 12, 2015 7:48 PM
    Monday, November 24, 2014 3:19 PM
  • This worked for me except that I had to enclose the zero in double quotes instead of single quotes like below. With single quotes, it was being treated as a table.

    "0" & FORMAT



    Best Regards, Dattatrey Sindol (Datta) | http://dattatreysindol.com

    Thursday, December 10, 2015 7:59 PM
  • For completeness sake, I am updating this post with the following information:

    My original question was based on SSAS 2012. Apparently SSAS 2016 will include additional features, possibly a duration type field, that will render workarounds moot.

    Regards,

    P.

    Friday, December 11, 2015 9:20 AM
  • You're absolutely right, Dattatrey. I don't know how that slipped past me. I've updated the original to have proper double quotes.

    GNet Group BI Consultant

    Saturday, December 12, 2015 7:49 PM