0

Format an measure of time ( calculated ) as MM:SS v. decimal

I have two fields:   Number of minutes, Number of Customers.   If I have values 9 and 2 and divide I get 4.5       

 

I would like to be able to display 4:30 on my insight.

 

What is the easiest/best/most replicable way to do this? 

7replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • how do you plan to display this data?

    what is your use case?

    Like
  • Our immediate need is in a list column "Average Time" which would display 4.5 minutes as 4:30    Eventually we'd also like it to display that way on a visualization ( e.g. a gauge )   

    Like
  • You can try this formula for KPI to convert 4.5 to 4:30 (4.5 can be replaced with an attribute from table):

    Like
  • This will show KPI like this:

    Like
  • in case you want to copy paste this formula and test:

    max(

    concat(

    splitPart(string(  4.5),".",1),

    ":",

    string(int(int(splitPart(string(4.5),".",2)) * 6))

    )

    )

    Like
  • Thank you Anurag.   I'm getting closer, but the above drops the leading zero in cases like 10.13 which results in 10:82 v. 10:08  

     

    I changed "int" to "double" and changing the multiplier to .6 v 6    Doing this I have wrestled it down to this ( below - apologies for the cut/past formatting) which seems to be working for the detail records, but looks like the "max" function forces an actual max on the two parts ( e.g. two records of 5:30 and 4:59 will result in 5:59  -- except in the solid gauge where it returns a zero (?) ) .  Unfortunately, but understandably, the "average" function doesn't like a string input.

     

    Can you give any other guidance?   

     

    Does this get automatically resolved in v5? ;-)    

     

    concat(

        (max(

            splitPart(

                string(

                    BS_QSR_Metrics.Total_Cars_and_Time.AVG_TIME

                ),

                ".",

                1

            )

        )),

        ":",

        max(

            substring(

                string(

                    double(

                        concat(

                            ".",

                            (splitPart(

                                string(

                                    BS_QSR_Metrics.Total_Cars_and_Time.AVG_TIME

                                ),

                                ".",

                                2

                            ))

                        )

                    ) * .60

                ),

                2,

                2

            )

        )

    )

    Like
    • R. A. Dawson Sr  this will be easier to do in 5.1. We are adding support for time interval.

      Like
Like Follow
  • 2 wk agoLast active
  • 7Replies
  • 21Views
  • 2 Following

Product Announcement

Incorta 5 is now Generally Available