Forum Discussion
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...
Its not that simple 🙂
You can do it in number of steps.
- Change your time column to text format. So it will look like normal time format but its text 00:04:00
- Split by delimiter :
- You will have 3 columns (One for HOURS, second is MINUTES and third is SECONDS) change type to WHOLE NUMBER
- From Add Column tab select CUSTOM COLUMN, name this column CustomH
- HOURS * 3600 (thats number of seconds in one hour)
- From Add Column tab select CUSTOM COLUMN, name this column CustomM
- MINUTES * 60 (number of seconds in one minute)
- From Add Column tab select CUSTOM COLUMN, name this column TimeInSeconds
- CustomH + CustomM + SECONDS
- 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
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
OK, it is for a measure, can still do Custom Formatting for a measure using the article I referenced. I'm not sure why that makes a difference.