Forum Discussion

khaycock's avatar
khaycock
Helper I
5 years ago
Solved

Summarising days/hours/mins

I have used the below to create a days/hours/mins field but it doesn't summarise because it's a text field. Is there any other calucation i can use which will allow me to summarise when there are multiple lines of days/hours/mins? (it's a ticketing system so ideally i would want to summarise)

 

 

Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi khaycock ,

     

    Will you consider to create a measure instead of a column?

    Measure = 
var minu = SUM(tickets[ticket time])
var hours = INT(minu/60)
var days = INT(hours/24)
var remainmin = MOD(minu,60)
var remainhours = MOD(hours,24)
return
days&" days, "&remainhours&" hours & "&remainmin&" minutes"

     

    Best Regards,

    Jay

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi khaycock ,

     

    The only thing you need to do is to replace sum() to average().

    And if you want a whole number, you could add int() before mod().

    Refer:

     

    Measure 2 = 
var minu = AVERAGE(tickets[ticket time])
var hours = INT(minu/60)
var days = INT(hours/24)
var remainmin = INT(MOD(minu,60))
var remainhours = MOD(hours,24)
return
days&" days, "&remainhours&" hours & "&remainmin&" minutes"

     

     

    Best Regards,

    Jay

9 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey khaycock ,

     

    what do you mean it doesn't summarize?

    The time should summarize depending on your filter context. Then at the end you return a text string with the summarized days, hours and minutes.

     

    Or where are you struggling?

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • khaycock's avatar
      khaycock
      Helper I
      5 years ago

      Sorry that I wasn't clear! I basically want to show a card that summarises all our tickets and shows how many days/hours/minutes a type of ticket has taken within a month. I can get the days/months/years to show on a line-by-line basis but when i summarise to a ticket type level, it doesn't work due to it not being able to sum.

       

      I also have a card which shows the average time a ticket took to close which is currently in a decimal number based on number of days, but would rather this showed as days/hours/mins too

    • khaycock's avatar
      khaycock
      Helper I
      5 years ago

      Can you help me with what to put in the custom format options please? I have a decimal field already which shows the days so can use that

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi khaycock ,

     

    Will you consider to create a measure instead of a column?

    Measure = 
var minu = SUM(tickets[ticket time])
var hours = INT(minu/60)
var days = INT(hours/24)
var remainmin = MOD(minu,60)
var remainhours = MOD(hours,24)
return
days&" days, "&remainhours&" hours & "&remainmin&" minutes"

     

    Best Regards,

    Jay

    • khaycock's avatar
      khaycock
      Helper I
      5 years ago

      If I wanted to use this to show average, could I just change the first variable like the below? Also how do I get it to not have all the decimal places?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi khaycock ,

         

        The only thing you need to do is to replace sum() to average().

        And if you want a whole number, you could add int() before mod().

        Refer:

         

        Measure 2 = 
var minu = AVERAGE(tickets[ticket time])
var hours = INT(minu/60)
var days = INT(hours/24)
var remainmin = INT(MOD(minu,60))
var remainhours = MOD(hours,24)
return
days&" days, "&remainhours&" hours & "&remainmin&" minutes"

         

         

        Best Regards,

        Jay