Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
stabben23
Partner - Master
Partner - Master

Find last nightshift

Hi,

I have timestamp transactions which is flagged With which shift who was working. I now have to find the latest Night shift.

I need some Expression that will pick out all "Green" rows. The problem occured when nightshift change workschedule and start to work before midnight.

My previous Expression was sum({<shift={'Night'} ,Date={"$(=today(1))"}>}value) The latest Night transactions was always on todays date.

Now I need something else to fetch rows that starts before midnight

The night schedule is 23:00-06.10

   

timeshift
2018-03-02 07:00Day
2018-03-02 07:01Day
2018-03-02 15:01Evening
2018-03-02 16:00Evening
2018-03-02 22:01Evening
2018-03-02 23:00Night
2018-03-03 06:00Night
2018-03-03 07:00Day
2018-03-03 07:10Day
2018-03-03 16:15Evening
2018-03-03 19:00Evening
2018-03-03 23:10Night
2018-03-04 02:00Night
2018-03-04 05:01Night
2018-03-04 09:00Day
2018-03-04 09:15Day
1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

The function DayStart() would be of help in this situation as it takes an offset as a parameter:

=Only( {<shift={Night},time={"=Date(Floor(DayStart(time,0,-1/24)+1))=Today(1)"}>} time )

The second parameter of DayStart says that the field time also contains time in addition to date when it is set to 0.

-1/24 is minus 1 hour obviously.

2018-04-04 12_03_46-Qlik Sense Desktop.png

View solution in original post

2 Replies
petter
Partner - Champion III
Partner - Champion III

The function DayStart() would be of help in this situation as it takes an offset as a parameter:

=Only( {<shift={Night},time={"=Date(Floor(DayStart(time,0,-1/24)+1))=Today(1)"}>} time )

The second parameter of DayStart says that the field time also contains time in addition to date when it is set to 0.

-1/24 is minus 1 hour obviously.

2018-04-04 12_03_46-Qlik Sense Desktop.png

stabben23
Partner - Master
Partner - Master
Author

Thats awesome petter-s‌, Works Perfect!