Find last nightshift

[stabben23]

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

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

[petter · Accepted solution]

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.

[stabben23]

Thats awesome petter-s‌, Works Perfect!