Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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..

17 Replies
Qliksenseranelx5
Contributor III
Contributor III
Author

The expression is returning a 1 for each instance where the conditions are true then summing that to get a total count. There is one instance that is returning a 2 that is making the result one higher than it actually is. I can’t figure out why a 2 will be returned by the expression. Photo attached 

Qliksenseranelx5
Contributor III
Contributor III
Author

 Veger I tried your expression and the same instance was counted twice. I’m analyzing the data to try and find the culprit.

JordyWegman
Partner - Master
Partner - Master

Hi Ranel,

This would suggest that the condition is true twice. Find out if you have duplicates in your data!

Jordy

Climber

Work smarter, not harder
Qliksenseranelx5
Contributor III
Contributor III
Author

Thanks Jordy that’s what I thought. I’ve been through the data for that instance and can’t find a duplicate. Despite that shouldn’t the >=1)1) part of the expression change anything greater than 1 to a 1?

JordyWegman
Partner - Master
Partner - Master

Hmm.. what happens when you add a distinct?

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

Maybe now it will work?

Jordy

Climber

Work smarter, not harder
Qliksenseranelx5
Contributor III
Contributor III
Author

Error in expression
Qliksenseranelx5
Contributor III
Contributor III
Author

What if we divide SubstringCount by SubstringCount to force a “1” then add the and piece? Would that work?

Qliksenseranelx5
Contributor III
Contributor III
Author

I created a calculated field in the data import using substring count and wrote a expression to count those with the employee parameters and it worked.