Comparing TimeStamps / Set Analysis

[hudam]

Good afternoon,

I am hoping to receive some help.  I am trying to determine an average for number of calls it takes an agent to receive human contact for the first time. I believe I either have a syntax error with set analysis, or somewhere along the way an AGGR function is being misused.

The key field I am working with is CallDateTime_CallLog which is a timestamp. I am also working with a master item [First Human Contact Date] defined by the following code,

             AGGR({<agent_name_CallLog={*}>} Min(CallDateTime_CallLog),contact_id_Master)

Below is one of two main lines of code I am trying to resolve (which makes use of the master item) :

Avg(aggr({<agent_name_CallLog={*}>} count({<CallDateTime_CallLog={"<=$(=AGGR[First Human Contact Date])"}>} distinct call_id),contact_id_Master))

The above code simply gives me the total number of calls for each contact_id_Master. 

The below syntax works only when a single contact_id_Master has been selected, and gives us the correct number.


Avg(aggr(count({<CallDateTime_CallLog ={"<=$(=AGGR(Min({<agent_name_CallLog {*}>}CallDateTime_CallLog),contact_id_Master))"}>} distinct call_id),contact_id_Master))




I notice issues with the comparison operation between the two timestamps. Issue being I can seem to swap <= to >= with no change. 

I am relatively new to Qlik and hope this was a suitable first post.  Any guidance in what is wrong here, or a proper direction to take would be appreciated.


I also considered the need for using a StructuredParameter with an aggr function, but no success there, and not entirely sure if I am receiving the single variable I am hoping to receive for the comparison, or a list.  Again, stand alone, the master item provides the proper timestamp.

[vinieme12 · Accepted solution]

Avg(aggr({<agent_name_CallLog={*}>} count({<CallDateTime_CallLog={"<=$(=AGGR[First Human Contact Date])"}>} distinct call_id),contact_id_Master))

This comparison doesn't seem right CallDateTime_CallLog < =[First Human Contact Date]) ??

[First Human Contact Date] is already your smallest value, how would any other  CallDateTime_CallLog record be smaller than [First Human Contact Date] ?

Can you explain what exactly you are trying to evaluate i.e  the business definition of this KPI

Also [First Human Contact Date] can be different for each contact_id_Master, therefore evaluating this in set analysis is not possible  as the expression will evaluate once for the entire chart and not each contact_id_Master row in the chart

I suggest you create new fields in your load script [First Human Contact Date]  , and a flagfield that does the comparison 

Temp;

Load * Dataset;

Left Join(Temp)

Load contact_id_Master

Min(CallDateTime_CallLog ) as [First Human Contact Date]

Resident Temp;

Final:

Load *,if(condition,true then 1,else 0) as flagfield

Resident Temp;

then expression

count({<agent_name_CallLog={*}, flagfield={'1'} >}distinct call_id)

[hudam]

Vineeth,

Thank you very much for the informative response.  I am going to be working on implementing the suggestions you gave.  I provided some more explanation below to ensure there is clarity.  I will hopefully resolve my issue today thanks to this response and will mark your response as 'solution' at that time.

       a call has 'human contact' if the agent_name_CallLog field is not null.  So for each contact_id_Master I am wanting to find how many calls it took until we made 'human contact' (I want to count all calls for each contact_id_Master up until and including the first call that has agent_name_CallLog populated).    Master and CallLog are separate tables. 

For the Master Item in place [First Human Contact] ; I was thinking my logic there was grabbing me the earliest date that has human contact - thus, there would potentially be calls preceding it without human contact that I would want to count.

But it sounds like I need to get a better understanding of the applicability of set analysis and when to use that vs. load editor. 

Moving to operating in the load editor was my next move, so I appreciate the insight on why set analysis would not work for this matter.

Respectfully,