topic Re: Calculating Hrs from 2 dates fields in QlikView

Calculating Hrs from 2 dates fields

Wed, 15 Oct 2014 21:51:47 GMT

How do you calculate hrs between columns L minus K ?

please see attached file for the format.

any help would be highly appreciated.

Re: Calculating Hrs from 2 dates fields

Wed, 15 Oct 2014 21:55:32 GMT

here is what i came up with but its not working out:

vHrsMult

* sum({$<$(sMTD(Production)),[Submission Transaction Types]={'QUOTED'},[Received Via]={'BSS'}>}

rangesum(

[Assign Date]

,-[Received Date]))

/ Count({$<$(sMTD(Production)),[Submission Transaction Types]={'QUOTED'},[Received Via]={'BSS'}>} [Assign Date])

Re: Calculating Hrs from 2 dates fields

maxgro — Wed, 15 Oct 2014 22:04:49 GMT

this is the difference in hour between two dates

replace the dates with your fields

(

date#('2/13/2014 12:27', 'MM/DD/YYYY hh:mm') -

date#('2/12/2014 15:03', 'MM/DD/YYYY hh:mm')

) * 24

Re: Calculating Hrs from 2 dates fields

Wed, 15 Oct 2014 22:10:55 GMT

how would you change this formula?

Where 'Assign Date' = L and 'Received Date' =K

vHrsMult

* sum({$<$(sMTD(Production)),[Submission Transaction Types]={'QUOTED'},[Received Via]={'BSS'}>}

rangesum(

[Assign Date]

,-[Received Date]))

/ Count({$<$(sMTD(Production)),[Submission Transaction Types]={'QUOTED'},[Received Via]={'BSS'}>} [Assign Date])

Re: Calculating Hrs from 2 dates fields

MarcoWedel — Wed, 15 Oct 2014 22:11:45 GMT

Hi,

one solution might be to create a seperate interval field to calculate with:

LOAD *,
    Interval([Assign Date]-[Received Date]) as Interval
FROM [http://community.qlik.com/servlet/JiveServlet/download/635003-132397/Example.txt] (txt, unicode, embedded labels, delimiter is '\t', msq);

hope this helps

regards

Marco

Re: Calculating Hrs from 2 dates fields

Wed, 15 Oct 2014 22:36:38 GMT

but how to implement "interval" into this :

vHrsMult

* sum({$<$(sMTD(Production)),[Submission Transaction Types]={'QUOTED'},[Received Via]={'BSS'}>}

rangesum(

[Assign Date]

,-[Received Date]))

/ Count({$<$(sMTD(Production)),[Submission Transaction Types]={'QUOTED'},[Received Via]={'BSS'}>} [Assign Date])

Calculating Hrs from 2 dates fields

Re: Calculating Hrs from 2 dates fields

Thu, 16 Oct 2014 00:33:21 GMT

anyone?

Re: Calculating Hrs from 2 dates fields

villegasi03 — Thu, 16 Oct 2014 02:32:09 GMT

I am sorry I a little confused. Just to be clear. You are asking how to get the difference of time between two values from two columns right? Sorry I am going to simplify this so its clear in this example,

row 1 column 1 = " your first timestamp "

row 1 column 2 = " your second timestamp "

and you need the difference between the two in hours?