topic Re: Calculating time in script in QlikView

Calculating time in script

kris_vliegen — Sat, 16 Nov 2024 20:25:50 GMT

Hi All,

I'm having a problem with calculating time.

In our planning-tooldatabase I have 2 time fields. (Planned time and the time spend)

Those are numeric for example if you get 16 hours in the database we zee 1600, if you get 2h and a half you se 230 in the database.

I can create a timefield in the script by using those 2

Interval((left(num(PlannedTime,'0000'),2) * 60 + Mid(num(PlannedTime,'0000'),3,2))/24/60,'hh:mm') as PlannedTimehhmm,
Time((left(num(PlannedTime,'0000'),2) * 60 + Mid(num(PlannedTime,'0000'),3,2))/24/60,'hh:mm') as PlannedTimeTijdhhmmTime

And the same for the SpendTime.

But how can I subtract those fields so I get the time that is available for this order?

Regards,

Kris

Re: Calculating time in script

tresB — Thu, 18 Jul 2019 10:12:28 GMT

Could you provide a sample data and expected output for that?

Re: Calculating time in script

kris_vliegen — Thu, 18 Jul 2019 11:25:14 GMT

Hi,

attached you can find an example.

Hope this is enough.

Re: Calculating time in script

tresB — Thu, 18 Jul 2019 12:50:59 GMT

Sorry, I am lost in your sample. Could you tell me what is expected output for two values you mentioned in op, 1600 (16 hours) and 230( 2 hours 30 mins)? 13 hours 30 mins right.

You could try like:

=interval(Time#(1600, 'hhmm')-Time#(230, 'hmm'), 'hh:mm')

Re: Calculating time in script

kris_vliegen — Thu, 18 Jul 2019 12:51:06 GMT

In this example the planned time is 8 h ( 08:00)

and the person has spend once 20 min and once 30 min -> together 00:50

So the value I would like to see as difference is 07:10

Re: Calculating time in script

tresB — Thu, 18 Jul 2019 12:54:35 GMT

Well, try this:

=interval(PlannedTimehhmm-Sum(Spendtime.URSpendTimehhmm), 'hh:mm')