Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

psaltzer
New Contributor

Function Syntax Help

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.

Tags (2)
8 Replies

Re: Function Syntax 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

psaltzer
New Contributor

Re: Function Syntax Help

Hi Rob,

Assigned To is the field for the agent name.

Prev Assigned To is the field for the agent it was before.

Re: Function Syntax Help

I understood that.  My question is, do you have another field for Agent name, like in an Agent table?

-Rob

psaltzer
New Contributor

Re: Function Syntax Help

Oh ok.

No.  No agent table.

Re: Function Syntax Help

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

http://masterssummit.com

http://qlikviewcookbook.com

psaltzer
New Contributor

Re: Function Syntax Help

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.

Re: Function Syntax Help

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

http://masterssummit.com

http://qlikviewcookbook.com

psaltzer
New Contributor

Re: Function Syntax Help

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.