I am requesting assistance with calculation ‘after hours’, ‘normal hours’ and over ‘weekend’ periods.
Weekends are defined from a Friday afternoon 18:00 to Monday morning 06:00.
After Hours is defined as 18:00 to 06:00, Monday to Friday.
Normal hours is 06:00 to 18:00, Monday to Friday.
This all gets defined from the field [Departure Time]. [Departure Time] has a timestamp 'YYYY-MM-DD hh:mm’.
Initially I tried calculating this as part of a field in the script, but it got the better of me. My thought now is calculate 3 separate parameter fields, ‘1 or 0’. I can then in set analysis select what I want to calculate.
Even with this plan, it has a few complications. If I use:
The Friday to the Weekend ‘overlaps’. I would really appreciate any guidance with this from people who have looked at something similar to this. I include a sample extract with qvw. Please note in the file there is a record I created manually. It is a record that appears after midnight:
Hi John, dates overlaps beacuse NormalHours and AfterHours are not filtering to remove the weekends, and the weekends were counting friday also as weekend instead of only NormalHours or AfterHours . The expressions are:
if(WeekDayNo <=4 and frac(START_TIME) > maketime($(vStartHour)),1,0 ) as Start_AfterHours ,
if(WeekDayNo <=4 and frac(START_TIME) < maketime($(vEndHour)),1,0 ) as Start_NormalHours ,
if( WeekDayNo >4 and WeekDayNo <=6, if(frac(START_TIME) > maketime($(vStartHour)) and frac(START_TIME) > maketime($(vEndHour)),1,0 ) ) as Weekends
Hello Ruben. Thank you very much for your solution. Unfortunately, the transaction I marked in Orange at 01:14, as a test, does not show as a after hours transaction. It shows up in the Start_NormalHours. Normal hours only starts at 06:00 in the morning.
Hello Ruben, that is so close.. The weekend time is a problem doing it this way because it 'looses' the daytime hours between 06:00 and 18:00. I have a solution, maybe not the slickest but will work, but my nested if's don't work out. Basically what I am saying is: