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: 
Zara
Contributor III
Contributor III

Count Values on certain days between certain times

Can someone please enlighten me as to where I am going wrong......

As a business we are looking to understand activity between our opening hours on specific days.

I am trying to say if the week day falls on a certain day between 9 - 5 count the id's.

count( {<WeekDay = {3,6}>} IF([calltime]>='09:00:00'AND calltime<='17:00:00', ID ))

In the data load the field is actually called "Call Date Time (CommsCalls)" and is in  DD/MM/YYYY HH:MM:SS format so i converted it using the below - 

time("Call Date Time (CommsCalls)") as calltime

Any ideas where I am going wrong?

Thanks in advance

1 Solution

Accepted Solutions
Stevie_Go
Contributor III
Contributor III

In load script, you could just make a bool-field for every call to say if the call was in working hours:

if(weekday(Date(Floor(Timestamp#([Call Date Time (CommsCalls)], 'DD/MM/YYYY hh:mm:ss')), 'YYYY/MM/DD')) < 6
and
(Time(Timestamp#([Call Date Time (CommsCalls)], 'DD/MM/YYYY hh:mm:ss'), 'hh') > 9 and Time(Timestamp#([Call Date Time (CommsCalls)], 'DD/MM/YYYY hh:mm:ss'), 'hh') < 17), 1, 0) as boolInWorkingHours

and then use your SA-String (replace the IF with a secund SA filter on the bool field) to count the IDs for the  calls  on specific weekdays (I suppose you have a master calendar in place?)

Qlik is somewhat "sensible" when it is not fully aware of the data type (you expect the IF clause to interpret a string as a number... my scripts fail when the datataype is more clear and I forget to explicitly cast it to num#() or text#()...

View solution in original post

2 Replies
Taoufiq_Zarra

@Zara 

can you share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Stevie_Go
Contributor III
Contributor III

In load script, you could just make a bool-field for every call to say if the call was in working hours:

if(weekday(Date(Floor(Timestamp#([Call Date Time (CommsCalls)], 'DD/MM/YYYY hh:mm:ss')), 'YYYY/MM/DD')) < 6
and
(Time(Timestamp#([Call Date Time (CommsCalls)], 'DD/MM/YYYY hh:mm:ss'), 'hh') > 9 and Time(Timestamp#([Call Date Time (CommsCalls)], 'DD/MM/YYYY hh:mm:ss'), 'hh') < 17), 1, 0) as boolInWorkingHours

and then use your SA-String (replace the IF with a secund SA filter on the bool field) to count the IDs for the  calls  on specific weekdays (I suppose you have a master calendar in place?)

Qlik is somewhat "sensible" when it is not fully aware of the data type (you expect the IF clause to interpret a string as a number... my scripts fail when the datataype is more clear and I forget to explicitly cast it to num#() or text#()...