Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qliksenseranelx5
Contributor III
Contributor III

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

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..

2 Solutions

Accepted Solutions
JordyWegman
Partner - Master
Partner - Master

HI Ranel,

You can use this, done by @sunny_talwar

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

Jordy

Climber

Work smarter, not harder

View solution in original post

JordyWegman
Partner - Master
Partner - Master

Hi Ranel,

That would be:

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

 Jordy

Climber

Work smarter, not harder

View solution in original post

17 Replies
JordyWegman
Partner - Master
Partner - Master

HI Ranel,

You can use this, done by @sunny_talwar

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

Jordy

Climber

Work smarter, not harder
Vegar
MVP
MVP

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

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

Qliksenseranelx5
Contributor III
Contributor III
Author

Thanks Jordy! Works exactly how I wanted.

Qliksenseranelx5
Contributor III
Contributor III
Author

Thanks Vegar for the reply

Qliksenseranelx5
Contributor III
Contributor III
Author

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
Partner - Master
Partner - Master

Hi Ranel,

That would be:

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

 Jordy

Climber

Work smarter, not harder
Qliksenseranelx5
Contributor III
Contributor III
Author

That did it! Thanks

Qliksenseranelx5
Contributor III
Contributor III
Author

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
Partner - Master
Partner - Master

Hi,

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

Jordy

Climber

Work smarter, not harder