Time calculation

[shamitshah]

Hi,

I have the following which have been converted to time, but I need a formula in Qlik to do the same:

Action Time             Converts to the following time

48240                       13.24.00

48249                       13.24.09

The formula is as follows with respect to the first example above:

Hour worked out as 48240/3600 = 13.4  (hour is 13)

Minutes - remainder/60 = 1440/60 = 24 (minutes is 24)

Seconds will 00 as there is no remainder

Any idea how I can incorporate the above in Qlik?

Thanks

Shamit

[hemanthaanichet]

Hi Shamit,

Can you conform me whether that Action Time is hours/minutes/seconds,

I think your requirement is Action Time has converts to HH:MM:SS or soomething else

Would you able to provide sample data that will be more helpful to work on it

Regards

Hemanth

[shamitshah]

Hi Hemanth,

That's correct  - Action time converts to HH.MM.SS

I have attached a sample file.

Thanks

[hemanthaanichet]

Hi Shamit,

One quick doubt is that action time is hours/mintues/seconds

Regards

Hemanth

[eromiyasimon]

hi Shamit,

use the below expression to get the value as expected

=num(ActionTime/3600,'##')&':'&num(mod(ActionTime,3600)/60,'##')&':'& num(mod(mod(ActionTime,3600),60),'00')

refer the attached QVW

hope this helps

[shamitshah]

Hi Hemanth,

I am not sure what you mean. The Action Time (this is the time) gets converted to the actual time in HH.MM.SS

Hope that helps.

Thanks

Shamit

[tresesco]

Try like:

Timestamp(time#(ActionTime,'sssss'), 'hh:mm:ss')

[Anonymous]

Hi Shamit,

Use following in script for required result

Time(Time#([Action Time],'ss'),'h:m:ss') AS True_Action_Time

above function is giving following result :

13:24:00

13:24:09

Thanks,

[shamitshah]

Hi Eromiya,

Is there anyway I can include that in the script so that the time appears as per your formula?

Thanks

Shamit

[eromiyasimon]

use poojas expression in Script if you want as script

Time(Time#([Action Time],'ss'),'h:m:ss') AS True_Action_Time