Calculate the average value of data in hh:mm format

發佈時間 : 28 Nov 2022
上次修改日期 : 29 Nov 2022

問題

How to calculate the average value of data in hh:mm format as demonstrated below:

環境

Tableau Desktop

答案

1. Connect to the attached data source.
2. Create a new calculated field as follows:
Name : time(hour)
Calculation : AVG(DATEPART('hour',[Time])*60+DATEPART('minute',[Time])) / 60
3. Create a new calculated field as follows:
Name : time (minute part)
Calculation : ([time(hour)] - INT(LEFT(STR([time(hour)]) , (FIND(STR([time(hour)]),".")-1)))) *60
4. Create a new calculated field as follows:
Name : time (hour part)
Calculation : LEFT(STR([time(hour)]) , (FIND(STR([time(hour)]),".")))
4. Create a new calculated field as follows:
Name : AVG_time (hh:mm)
Calculation : REPLACE((STR([time (hour part)])
+
LEFT(STR([time (minute part)]),FIND(STR([time (minute part)]),".")-1))
,".",":")
5. Drag AVG_time (hh:mm) to Text.