QlikSense Count If Expression that selects values similar to a Fuzzy Search

[Qliksenseranelx5]

HI Community i'm going to explain what i'm trying to do and hope someone can help.  I have a bar graph that is displaying the number of tickets a help desk has closed by month for the 2018 and 2019. In the data there is a column called "Catagory". The Catagory column list the departments each ticket impacted and it could be multiple separated by a comma like this (Accounting, Engineering, IT, Business) I need help writing an expression to only count tickets where IT is listed in the Catagory column. The expression i'm currently using is only returning a count where IT is the only department listed, it's not counting the tickets where IT is listed with other departments.  I know that i can do a fuzzy search using ~ outside of editing but I can't select all of those.  Please help..

[JordyWegman · Accepted solution]

HI Ranel,

You can use this, done by @sunny_talwar. 

Sum(If(SubStringCount(YourOwnField, 'IT') >= 1, 1))

Jordy

Climber

[Vegar]

The IF example avove will probably work, but an alternative approach is to use a SET in your expression.

=Count( {< Catagory = {"*, IT,*"}>} Catagory)

[Qliksenseranelx5]

Thanks Jordy! Works exactly how I wanted.

[Qliksenseranelx5]

Thanks Vegar for the reply

[Qliksenseranelx5]

Jordy adding on to that there is a column titled Employee Name. I need the results from the previous solution to only be tickets by a specific employee. What would the and expression be that I’d need to add?

[JordyWegman · Accepted solution]

Hi Ranel,

That would be:

Sum(If(SubStringCount(YourOwnField, 'IT') >= 1 and [Employee Name] = 'EmployeeName', 1))

 Jordy

Climber

[Qliksenseranelx5]

That did it! Thanks

[Qliksenseranelx5]

Jordy what would be the condition where a 2 is returned to sum instead of a 1? This is happening for a ticket which is throwing off the actual count by 1.

[JordyWegman]

Hi,

Could you maybe send and example? Like  a screenshot? I don't really get the problem now.

Jordy

Climber