Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
qlikmeplease
Contributor III

AND OR Nested IF statements

Hi,

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.

Example:

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



OR


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

AND

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

24.04.2016 10:00

Hope this helps,Any advice will be appreciated.

Thanks in advance !

2 Replies
MVP & Luminary
MVP & Luminary

Re: AND OR Nested IF statements

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')


talk is cheap, supply exceeds demand
MVP
MVP

Re: AND OR Nested IF statements

Maybe something like

If(

DayName( TIMESTAMPFIELD - Maketime(21)) = '22.04.2016' And Hour(TIMESTAMPFIELD - Maketime(21) ) <=12, 'X','')