topic Re: Convert Time to number in QlikView

Convert Time to number

jood_ahmad — Wed, 25 Nov 2020 16:16:04 GMT

Hello Dear,

how can i convert hours to number(integer) like:

i have this total working hours for my team in one day 29:14:06 H and i need to get the cost for them the normal calculation that i do it in excel is:

total working hours ((29*60)+(14'mm'))/60 so i will get this number 29.233 after that i do 29.233 * 24.5' Price per hour' / 19 '#of Orders'

29.233 * 24.5 / 19 = 37.69 .

so to summarize it : i have to column 1. working hours. 2. # of Orders. and the price is fix 24.5 .

i try to use Interval function and calculate it but does not give me the correct number.

any help pleas how con do this.

Re: Convert Time to number

antoniotiman — Thu, 13 Oct 2016 09:18:11 GMT

Hi,

try

(SubField(YouTime,':',1)+SubField(YourTime,':',2)/60)*Price/NrOrder

Regards,

Antonio

Re: Convert Time to number

sunny_talwar — Thu, 13 Oct 2016 09:33:13 GMT

This might also work:

Floor(Interval#(SubField('29:14:06 H', ' ', 1), 'h:mm:ss'), 1/1440)*24

24.5

[# of Order]

Re: Convert Time to number

awhitfield — Thu, 13 Oct 2016 10:27:25 GMT

Hi Ahmad,

Please check out the attached:

Script sample:

Data_1:

Load * Inline

[

Work_Hrs, Price, #Orders

29:14:09, 24.5, 19

];

Data_2:

Load

Num(Sum(SubField(Work_Hrs,':',1)* 60 + SubField(Work_Hrs,':',2))/60,'#,##.00') as Decimal_Hours

Resident Data_1;

Chart Expression:

=Sum(Decimal_Hours * Price) / #Orders

Re: Convert Time to number

jood_ahmad — Sun, 16 Oct 2016 12:35:34 GMT

HI Sunny,

can you explain the formula please.

why we use this and what is the 1/1440 ?

Re: Convert Time to number

sunny_talwar — Sun, 16 Oct 2016 12:55:00 GMT

So interval was used to interpret your time and then I am using Floor is used to Floor your timestamp to the nearest minute. (1/1440 where 1440 is made of 24*60 where 24 is the number of hours and 60 is number of minutes) and finally multiplying with 24 to convert them into hours.

Does it make sense?