Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
pskumars
Partner - Contributor III
Partner - Contributor III

business hours and rest as non business hours.

HI All,

I Have date data below.

i need to calculate time between 8:00 to 20:00 as business hours and rest as non business hours.

 

DateTime
11/16/2016 15:00
11/16/2016 15:30
11/17/2016 14:30
11/21/2016 9:30
11/21/2016 10:00
11/21/2016 10:30
11/21/2016 11:00
11/21/2016 11:30
11/22/2016 7:30
11/22/2016 12:00
11/23/2016 11:30
11/24/2016 11:30
11/29/2016 9:30
12/1/2016 10:30
12/1/2016 11:00
1 Solution

Accepted Solutions
sunny_talwar

May be try this

If(Frac(DateTime) > MakeTime(7, 59, 59) and Frac(DateTime) < MakeTime(20, 0, 1), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag]

View solution in original post

8 Replies
sunny_talwar

May be like this

LOAD DateTime,

     If(DateTime >= MakeDate(8) and DateTime <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag],

     ....

FROM ...

pskumars
Partner - Contributor III
Partner - Contributor III
Author

Not working Sunny,thanks for suggestion

neelamsaroha157
Specialist II
Specialist II

Does this work for you..

pskumars
Partner - Contributor III
Partner - Contributor III
Author

no

sunny_talwar

Forgot to add Frac function

LOAD DateTime,

    If(Frac(DateTime) >= MakeDate(8) and Frac(DateTime) <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag],

    ....

FROM ...

sunny_talwar

Found another issue

If(Frac(DateTime) >= MakeTime(8) and Frac(DateTime) <= MakeTime(20), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag]

pskumars
Partner - Contributor III
Partner - Contributor III
Author

Hi Sunny ,

it works but there is an issue , 20:00 also ates.JPGdding as non business hours.

sunny_talwar

May be try this

If(Frac(DateTime) > MakeTime(7, 59, 59) and Frac(DateTime) < MakeTime(20, 0, 1), 'Business Hours', 'Non-Business Hours') as [Business Hour Flag]