Count Values on certain days between certain times

[Zara]

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

[Taoufiq_Zarra]

@Zara 

can you share a sample data and the expected output ?

[Stevie_Go · Accepted solution]

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#()...