Calculate normal, after and weekend periods

[johngouws]

Good day.

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:

if( WeekDayNo >3 and WeekDayNo <=6, if(frac(START_TIME) > maketime($(vStartHour)) and frac(START_TIME) > maketime($(vEndHour)),1,0 ) )          as Weekends

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:

‘AAAAAAA

2018-02-02 01:14

2018-02-02 12:04

1000

Thank you very much in advance.

[rubenmarin]

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

[johngouws]

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.

Thank you very much for giving input.

[rubenmarin]

Right, I did it too quick, can you try?:

if(WeekDayNo <=4 and frac(START_TIME) >= maketime($(vDayStart))and frac(START_TIME) <= maketime($(vDayEnd)),1,0 ) as Start_NormalHours,

  if(WeekDayNo <=4 and (frac(START_TIME) < maketime($(vDayStart)) or frac(START_TIME) > maketime($(vEndHour))),1,0 ) as Start_AfterHours ,

if( WeekDayNo >4 and WeekDayNo <=6, if(frac(START_TIME) > maketime($(vStartHour)) and frac(START_TIME) > maketime($(vEndHour)),1,0 ) ) as Weekends

[johngouws]

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:

if( WeekDayNo =4, if(frac(START_TIME) > maketime($(vDayEnd)),1,0 ) ) as Weekends_Friday - gives Friday evenings.

if( WeekDayNo >=5 and WeekDayNo <=6, 1,0 )  as Weekends_Sat_Sun, - gives Saturdays and Sundays.

if( WeekDayNo =0, if(frac(START_TIME) < maketime($(vEndHour)),1,0 ) ) as Weekends_Monday, - Gives Monday mornings.

These periods define what is classed as the Weekend. If I put this logic together it does not work. Could you see where I have the problem?

if(WeekDayNo =4, if(frac(START_TIME) > maketime($(vDayEnd))), 1,

     if(WeekDayNo >=5 and WeekDayNo <=6), 1,

     if(WeekDayNo =0, if(frac(START_TIME) < maketime($(vEndHour))), 1,

0)) as [Weekends]

Thank you very much for your help.

[rubenmarin · Accepted solution]

If will be easier if you group conditions with parenthesys instead of nested if's:

if(WeekDayNo <=4 and frac(START_TIME) >= maketime($(vDayStart))and frac(START_TIME) <= maketime($(vDayEnd)),1,0 ) as Start_NormalHours,

If((WeekDayNo=0 and frac(START_TIME)> maketime($(vDayEnd))) or (WeekDayNo=4 and frac(START_TIME)< maketime($(vDayStart)))

     or (WeekDayNo>0 and  WeekDayNo<4 and (frac(START_TIME) < maketime($(vDayStart)) or frac(START_TIME) > maketime($(vDayEnd)))),1,0 ) as Start_AfterHours ,

  If((WeekDayNo=0 and frac(START_TIME)< maketime($(vDayStart))) or (WeekDayNo=4 and frac(START_TIME)> maketime($(vDayEnd)))

     or (Match(WeekDayNo,5,6)),1,0) as Weekends

[johngouws]

Hello Ruben, I have done a few validations and it works out 100%. I was concerned about that one highlighted transaction!

My problem now is that I need to learn from what you did...

Thank you very much for your help. If I could give you more awards, I would..

All the best.