Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jayati_shrivast
Contributor III
Contributor III

Time segregation based on date field

Hi All,

I have a date field in the format DD/MM/YYYY hh:mm:ss, I need to segregate time based on inoffice and outoffice

for IN OFFICE condition is " 8:30 am to 7:30 pm and rest of the time should come under OUTOFFICE.

I should get the table layout as follows:

date                                                    COLUMN1   

-----------------------------------------------------------------

04/12/2018 08:30:23                         INOFFICE

04/12/2018 20:08:23                        OUTOFFICE

 

For this under , COLUMN1 expression tab I am applying following condition

=if(time(date) >= '08:30:00' and time(date)<= '17:30:00' ,'InOffice',
if(time(date)>= '17:31:00' and time(date)<= '08:29:00' ,'InOffice'))

This condition is coming out to be OK in the expression tab, but I am not getting the values in the front end under COLUMN1.

 

pls suggest me if there is any other method by which I can implement this.

Thanks

 

1 Solution

Accepted Solutions
Vince_CH
Creator II
Creator II

pls try this, this will extract only the time absolutely.
If(time(frac(Date),'h:mm:ss')>='08:00:00' and time(frac(Date),'h:mm:ss')<='20:00:00','Inoffice','Outoffice')

View solution in original post

8 Replies
Vince_CH
Creator II
Creator II

please try like following:
=if(time(date) >= '08:30:00' and time(date)<= '17:30:00' ,'InOffice', 'OutOffice')
jayati_shrivast
Contributor III
Contributor III

I applied this condition , it worked, but I am getting OUTOFFICE for all rows , even where INOFFICE should come, there also OUTOFFICE is coming.....
Vince_CH
Creator II
Creator II

Hello, can you add a field like time(date) to see if the time format coming out is exactly same as '08:30:00' or else?
Vince_CH
Creator II
Creator II

=if(time(date) >= '08:30:00' and time(date)<= '17:30:00' ,COLUMN1='InOffice', COLUMN1='OutOffice'),

pls try this?
jayati_shrivast
Contributor III
Contributor III

There is no such field as COLUMN1, I have taken a dimension in front end in the table and labelled it as COLUMN1 and applied if condition in its expression tab.
also I check the format it is hh:mm:ss
Vince_CH
Creator II
Creator II

pls try this, this will extract only the time absolutely.
If(time(frac(Date),'h:mm:ss')>='08:00:00' and time(frac(Date),'h:mm:ss')<='20:00:00','Inoffice','Outoffice')

View solution in original post

Vince_CH
Creator II
Creator II

Hello Jayati_shrivast , is the issue resolved? kindly please mark the correct the reply as answer if it is acceptable, or you can kinldy post your correct expressions here for the reference of others in the future. thank you!
jayati_shrivast
Contributor III
Contributor III

Thanks, this function worked :-).