topic Re: Calculate normal, after and weekend periods in QlikView

Calculate normal, after and weekend periods

johngouws — Mon, 29 Oct 2018 05:46:08 GMT

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.

Re: Calculate normal, after and weekend periods

rubenmarin — Mon, 29 Oct 2018 09:46:12 GMT

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

Re: Calculate normal, after and weekend periods

johngouws — Mon, 29 Oct 2018 11:59:35 GMT

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.

Re: Calculate normal, after and weekend periods

rubenmarin — Mon, 29 Oct 2018 12:42:37 GMT

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

Re: Calculate normal, after and weekend periods

johngouws — Tue, 30 Oct 2018 02:55:39 GMT

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.

Re: Calculate normal, after and weekend periods

rubenmarin — Tue, 30 Oct 2018 07:27:20 GMT

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

Re: Calculate normal, after and weekend periods

johngouws — Tue, 30 Oct 2018 08:11:13 GMT

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.