Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
johngouws
Partner - Specialist
Partner - Specialist

Calculate normal, after and weekend periods

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.

1 Solution

Accepted Solutions
rubenmarin

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

View solution in original post

6 Replies
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
Partner - Specialist
Partner - Specialist
Author

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
Partner - Specialist
Partner - Specialist
Author

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

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
Partner - Specialist
Partner - Specialist
Author

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..

Star.jpg

All the best.