topic Re: Comparing TimeStamps / Set Analysis in App Development

Comparing TimeStamps / Set Analysis

hudam — Wed, 04 May 2022 20:08:41 GMT

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.

Re: Comparing TimeStamps / Set Analysis

vinieme12 — Thu, 05 May 2022 03:12:35 GMT

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)

Re: Comparing TimeStamps / Set Analysis

hudam — Thu, 05 May 2022 10:00:38 GMT

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,