Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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. ???
You should be able to do this in the pivot table I think
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])
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
I have added pivot for reference
Awesome, will check this in a little bit.... have to run for a meeting
Hi Friends,
Appreciate if any one can help me from here please.
Like this