topic Re: Re: Time Calculations in QlikView

Time Calculations

Mon, 19 May 2014 09:16:59 GMT

Hi All

I need to find out how I can multiply a number of hours by expected personnel, to get total hours booked to event. the calculation to get the Hours are

if(interval(EndTime - StartTime,'hh:mm')<0, interval(Date(EndTime,'ddmmyy')+1 - StartTime,'hh:mm'),interval(EndTime - StartTime,'hh:mm') )as [Total_Hours] and the personnel Expected is a sum of numbers. so the total booked hours should be 21:00, but I cant workout how I can calculate a time calculation by a number calculation. am i missing something

can anyone help.

thanks in advance

Re: Time Calculations

tresB — Mon, 19 May 2014 09:23:49 GMT

Could you post your sample qvw?

Re: Time Calculations

hic — Mon, 19 May 2014 09:38:46 GMT

First, you have too many formatting functions in your expression. Remove them: They do not change the calculations; they just prevent you from seeing the structure of your expression. Instead of

If( interval(EndTime - StartTime,'hh:mm')<0,

interval(Date(EndTime,'ddmmyy')+1 - StartTime,'hh:mm'),

interval(EndTime - StartTime,'hh:mm')

) as [Total_Hours]

you should write

Interval(

If( EndTime - StartTime <0,

EndTime +1 - StartTime,

EndTime - StartTime ),

'hh:mm' ) as [Total_Hours]

If you want to format it, you should put the formatting function as the outermost function.

Secondly, if EndTime and StartTime are correctly interpreted, they have numeric values where a difference of one day is 1, and a difference of one hour is 1/24. This means that you can get the number of hours through

(EndTime - StartTime) * 24

HIC

Re: Time Calculations

Mon, 19 May 2014 10:01:47 GMT

Hi tresesco

I have attached an example now.

thanks

Re: Time Calculations

tresB — Mon, 19 May 2014 10:03:20 GMT

user id, password?

Re: Time Calculations

senpradip007 — Mon, 19 May 2014 10:04:08 GMT

Can you share the credentials to login to your application?

Re: Time Calculations

Anonymous — Mon, 19 May 2014 10:06:13 GMT

I was trying open your sample document but it was asking the user ID

try with below

Expected hours = Personel expected*(Totalhours*24)

In total hours you can use your actual expression

Re: Time Calculations

Mon, 19 May 2014 10:09:53 GMT

Hi sorry about that i forgot it had section access. i have remove this now and re-uploaded

thanks in advance

Re: Time Calculations

Anonymous — Mon, 19 May 2014 10:18:12 GMT

I tried and it worked

DDT_Hours*24)*[Personnel Expected]

Re: Time Calculations

Mon, 19 May 2014 10:28:26 GMT

Hi Jebamalai

thanks for taking a look,i need the output to be formated to hh:mm. can you help?

regards

James

Re: Time Calculations

Anonymous — Mon, 19 May 2014 10:32:48 GMT

change your expression to

(DDT_Hours)*[Personnel Expected]

and change the format to Time->hh:mm from the number tab

it works

Re: Time Calculations

Mon, 19 May 2014 10:37:13 GMT

i have already changed this and it just shows 00:00 for all values now! are you able to amend the example?

thanks Jebamalai

Re: Re: Time Calculations

Anonymous — Mon, 19 May 2014 10:40:56 GMT

Hi James

Pls check and let me know

Regards

Jeba

Re: Time Calculations

Mon, 19 May 2014 10:49:13 GMT

Hi Jeba

The third calculation isnt correct it should show 50:00, it shows 02:00 in the example

regards

James

Re: Re: Time Calculations

Anonymous — Mon, 19 May 2014 11:01:29 GMT

Oh sorry.

Pfa the updated one

Re: Time Calculations

Mon, 19 May 2014 11:08:03 GMT

Hi Jeba

that's perfect thanks for your help

regards

James