Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! __REGISTER TODAY__ and save!

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Calculate normal, after and weekend periods

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

johngouws

Partner - Specialist

2018-10-29
01:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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(

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,164 Views

1 Solution

Accepted Solutions

rubenmarin

MVP

2018-10-30
03:27 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,097 Views

6 Replies

rubenmarin

MVP

2018-10-29
05:46 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 f**rac(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

1,097 Views

johngouws

Partner - Specialist

2018-10-29
07:59 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,097 Views

rubenmarin

MVP

2018-10-29
08:42 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,097 Views

johngouws

Partner - Specialist

2018-10-29
10:55 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,097 Views

rubenmarin

MVP

2018-10-30
03:27 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,098 Views

johngouws

Partner - Specialist

2018-10-30
04:11 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.