Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I've been going over the help files, and trying to piece together what needs to be done, and I can't figure it out. I'm hoping someone can help me. I have an app that contains data regarding the various tickets that come through our ticketing system. I am trying to come up with a function that will count all the tickets that an agent was assigned at any one point in time, not just the ones that they resolved. The fields that I believe hold the key to this are:
Incident ID, Assigned To, Prev Assigned To, Incident State, Prev Incident State
According to http://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-modifiers... there’s an example given that I believe will meet my needs but I don’t understand the syntax which is
sum( {$<Customer = P({1<Product={‘Shoe’}>} Customer)>} Sales )
I came up with
count( {$<[Assigned To] = P({1<[Prev Assigned To]={}>} [Assigned To])>} [Incident ID] )
but that didn’t return anything. Did I write the statement incorrectly, or is there a better way to do it?
When written correctly, if ticket 123456 was worked by John Doe, and then later on it was also worked by Jane Doe, it should be included in both of their counts for total number of tickets worked.
Thanks again for all of your help.
Is [Assigned_To] your field for the agent name? Or there an agent name field you are trying to match up with Assigned/Previous like?
AgentName = [Assigned To] OR AgentName = [Prev Assigned To]
-Rob
Hi Rob,
Assigned To is the field for the agent name.
Prev Assigned To is the field for the agent it was before.
I understood that. My question is, do you have another field for Agent name, like in an Agent table?
-Rob
Oh ok.
No. No agent table.
I'm not sure it's possible unless you have a separate field for the Agent name. So go ahead and create one in the script that links back to the incidents. Your chart then uses Dimension [Agent Name] and Measure:
count(DISTINCT Incident_ID)
See attached example QVF and this sample script.
Tickets:
LOAD *, RecNo() as INCIDENT_ID INLINE [
Assigned To, Prev Assigned To
a, b
a, b
b,
b, e
c, a
]
;
Agents_Tickets:
Load
INCIDENT_ID,
[Assigned To] as [Agent Name]
Resident Tickets
;
Load
INCIDENT_ID,
[Prev Assigned To] as [Agent Name]
Resident Tickets
where len([Prev Assigned To]) > 0
;
-Rob
Hi Rob,
Thank you for the script you came up with. I apologize for not responding sooner, but I was trying to meet work deadlines and wasn't able to come back to this. In trying to follow along with the script, what does the a,b, c lines represent? I tried to use it with a sample data set and I'm not seeing what was changed.
The inline data is just of way of including source data inline in the script -- great for sample data --without having to read an external file. Think of it like a CSV file where the first line
Assigned To, Prev Assigned To
represents the column names and subsequent lines are data rows.
So the a b c values are just dummy agent names I made up.
-Rob
Got it. Thanks. I still couldn't make it work the way I wanted it to, but at least you gave me some good ideas to try. I appreciate all your help.