Qlik Community

Ask a Question

New to Qlik Sense

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

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
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
Partner

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
Partner

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
Partner

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

Vegar
Partner
Partner

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

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
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
Partner

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

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
Partner

Hi,

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

Jordy

Climber

Work smarter, not harder