Forum Discussion

Bassehave's avatar
Bassehave
Helper I
6 years ago
Solved

Calculate Time over multiple months

Hello everyone, I'm having an issue getting an average of time over multiple months. Let me give an example. I'm converting Time to Decimal to be able to multiply with Calls.   Date Calls Tim...
Need Help
  • Abduvali's avatar
    Abduvali
    6 years ago

    Bassehave ,

     

    Its not that simple 🙂

     

    You can do it in number of steps.

    1. Change your time column to text format. So it will look like normal time format but its text 00:04:00 
    2. Split by delimiter :
    3. You will have 3 columns (One for HOURS, second is MINUTES and third is SECONDS) change type to WHOLE NUMBER
    4. From Add Column tab select CUSTOM COLUMN, name this column CustomH 
      1. HOURS * 3600 (thats number of seconds in one hour)
    5. From Add Column tab select CUSTOM COLUMN, name this column CustomM
      1. MINUTES * 60 (number of seconds in one minute)
    6. From Add Column tab select CUSTOM COLUMN, name this column TimeInSeconds
      1. CustomH + CustomM + SECONDS
    7. Close & Apply

    Now you converted your time into seconds =D

     

     

    Then create new measure and copy and paste my logic but just use your table name and column name.

     

    Hope it clear now.

     

    Abduvali

Abduvali's avatar
Abduvali
Skilled Sharer
6 years ago

Greg_Deckler ,

 

No, the code is for a MEASURE.

 

In Edit Query mode find your time column and convert it to secods!

Do you know how to achieve the following???

 

If yes, then just use that TimeInSeconds column in the code provided below: (this code you will use to create NEW MEASURE in the report view)

 

Avg Time = 

var TimeInSeconds = SUM(YourTableName [Yuor TimeInSeconds column]) / calls)
var a = INT((TimeInSeconds)/3600)
var b = LEFT(INT(MOD(TimeInSeconds,3600)/60),2)
var c = ROUND(LEFT(MOD(MOD(TimeInSeconds,3600),60),2),0)
 
var h = IF(LEN(a)=1,CONCATENATE("0",a),CONCATENATE("", a))
var m = IF(LEN(b)=1,CONCATENATE("0",b),CONCATENATE("",b))
var s = IF(LEN(c)=1,CONCATENATE("0",c),CONCATENATE("",c))

 

return
CONCATENATE(h,CONCATENATE(":",CONCATENATE(m,CONCATENATE(":",s))))
 
Let me know if you have any other questions.
 
Abduvali
 
 
Bassehave's avatar
Bassehave
Helper I
6 years ago

Hello Abduvali,

 

I have removed all of my old formatting to start from the beginning.

Now I converted my time column into the "time" format. I don't see anywhere to convert it to seconds? Under "Date & Time Column" I can choose Seconds, but that only extracts seconds from the time.

 

Am I missing something here?

 

 

 

  • Abduvali's avatar
    Abduvali
    Skilled Sharer
    6 years ago

    Bassehave ,

     

    Its not that simple 🙂

     

    You can do it in number of steps.

    1. Change your time column to text format. So it will look like normal time format but its text 00:04:00 
    2. Split by delimiter :
    3. You will have 3 columns (One for HOURS, second is MINUTES and third is SECONDS) change type to WHOLE NUMBER
    4. From Add Column tab select CUSTOM COLUMN, name this column CustomH 
      1. HOURS * 3600 (thats number of seconds in one hour)
    5. From Add Column tab select CUSTOM COLUMN, name this column CustomM
      1. MINUTES * 60 (number of seconds in one minute)
    6. From Add Column tab select CUSTOM COLUMN, name this column TimeInSeconds
      1. CustomH + CustomM + SECONDS
    7. Close & Apply

    Now you converted your time into seconds =D

     

     

    Then create new measure and copy and paste my logic but just use your table name and column name.

     

    Hope it clear now.

     

    Abduvali

    • Bassehave's avatar
      Bassehave
      Helper I
      6 years ago

      Thanks a lot for the clarification!

       

      I will stop looking for the one-button-does-all 🙂

       

      And again, thanks a lot for your support, I have marked the reply as a solution 🙂

      • Abduvali's avatar
        Abduvali
        Skilled Sharer
        6 years ago

        Bassehave ,

         

        No problem mate. Glad it worked!

         

        Regards

        Abduvali