Discussion Board for collaboration related to Creating Analytics for QlikView.
i want to create a flag if a date falls into two dates (day or (day +1)) AND the hours in the day falls between 21:00 and 09:00
because of the date change at midnight i need to ensure that both dates are checked.
this is in order to divine Shift times: dayshift is between 09:00-21:00 (will be the same day) and nightshift will be 21:00-09:00 meaning that the day increases by one.
Shift will run from 22.06.2016 21:00 to 23.06.2016 09:00. I am currently using these dates in TIMESTAMP format.
To get an idea of what i want to accomplish please have a look at the code below :
Day(Timestamp([TSA_ROUTE_DEP_1],'YYYY/MM/DD hh:mm:ss')) /////this will be in order to get the current date
Day(Timestamp(Interval(Timestamp([TSA_ROUTE_DEP_1],'YYYY/MM/DD hh:mm:ss') + '24:00:00','hh:mm:ss'),'YYYY/MM/DD hh:mm:ss')) //// this will increase the day + 1
Hour(TIMESTAMP([TSA_ROUTE_DEP_1],'hh:mm:ss')) //////////// >=21:00 <=09:00
In summary, i want to check IF a date is equal to the date selected or the day after and flag all the data within the hours of 21:00 and 09:00.
For example if Nightshift is selected for the 22nd the following should flag.
22.04.2016 21:00 X
23.04.2016 08:50 X
22.04.2016 23:00 X
Hope this helps,Any advice will be appreciated.
Thanks in advance !
If( [TSA_ROUTE_DEP_1] >= MakeDate(Year([TSA_ROUTE_DEP_1],Month([TSA_ROUTE_DEP_1]),22)+7/8) and
TSA_ROUTE_DEP_1] <= MakeDate(Year([TSA_ROUTE_DEP_1],Month([TSA_ROUTE_DEP_1]),23)+3/8), 'X')
Maybe something like
DayName( TIMESTAMPFIELD - Maketime(21)) = '22.04.2016' And Hour(TIMESTAMPFIELD - Maketime(21) ) <=12, 'X','')