topic Re: Calculating Hours Between DateTime Stamp & Change Date in QlikView

Calculating Hours Between DateTime Stamp & Change Date

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

Hi All,

I am trying to calculate total hour between each In & Out done by agents.

And also I am try to add Actual Login Date. If any agent login between 12.00 AM to 5.00 AM then date should be minus 1 day else same date. I have attached demo file hope that will help.

Re: Calculating Hours Between DateTime Stamp & Change Date

sunny_talwar — Wed, 13 Sep 2017 12:17:16 GMT

May be this

Table:

LOAD Name,

Time,

Date,

[Date/Time],

[IN/Out]

FROM

[..\..\Downloads\Book1.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

FinalTable:

LOAD *,

If(Name = Previous(Name),

If([IN/Out] = 'In' and Previous([IN/Out]) = 'Out',

Alt(Interval(Previous([Date/Time]) - [Date/Time], 'h:mm'), 'Out Miss'), 'Out Miss'), 'Out Miss') as [Total Hour],

Date(If(Time <= MakeTime(5) and [IN/Out] = 'In', Date - 1, Date)) as [Actual Login Date]

Resident Table

Order By Name, [Date/Time] desc;

DROP Table Table;

Re: Calculating Hours Between DateTime Stamp & Change Date

DipeshVadgama — Wed, 13 Sep 2017 12:51:38 GMT

Great Thank, This works. with above code if I also want to add what is a Max (Date & Time) and Min (Date & time) to actual login date in pivot or at loading. ???

Re: Calculating Hours Between DateTime Stamp & Change Date

sunny_talwar — Wed, 13 Sep 2017 12:57:42 GMT

You should be able to do this in the pivot table I think

Re: Calculating Hours Between DateTime Stamp & Change Date

DipeshVadgama — Wed, 13 Sep 2017 14:03:05 GMT

Hi,

I am using below expressions in pivots but it not giving me correct date & time

In load I have added below line

Date(If(Time <= MakeTime(5) and [IN/Out] = 'In', Date - 1, Date))+Time as [Actual Login Date/Time]

and in pivot I have added below expressions

min([Actual Login Date/Time])

max([Actual Login Date/Time])

Re: Calculating Hours Between DateTime Stamp & Change Date

sunny_talwar — Wed, 13 Sep 2017 14:11:58 GMT

Why are you adding time to this? I am not sure what you are looking to get... can you update your Excel to explain what you are looking for

Re: Calculating Hours Between DateTime Stamp & Change Date

DipeshVadgama — Wed, 13 Sep 2017 14:21:48 GMT

I have added pivot for reference

Re: Calculating Hours Between DateTime Stamp & Change Date

sunny_talwar — Wed, 13 Sep 2017 14:29:35 GMT

Awesome, will check this in a little bit.... have to run for a meeting

Re: Calculating Hours Between DateTime Stamp & Change Date

DipeshVadgama — Wed, 13 Sep 2017 15:44:15 GMT

Hi Friends,

Appreciate if any one can help me from here please.

Re: Calculating Hours Between DateTime Stamp & Change Date

sunny_talwar — Wed, 13 Sep 2017 16:53:26 GMT

Like this