topic Count Values on certain days between certain times in App Development

Count Values on certain days between certain times

Zara — Mon, 08 Mar 2021 18:36:58 GMT

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

Re: Count Values on certain days between certain times

Taoufiq_Zarra — Mon, 08 Mar 2021 18:59:29 GMT

@Zara

can you share a sample data and the expected output ?

Re: Count Values on certain days between certain times

Stevie_Go — Tue, 09 Mar 2021 07:35:07 GMT

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