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

[Qliksenseranelx5]

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]

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

[JordyWegman]

Hi Ranel,

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

Jordy

Climber

[Qliksenseranelx5]

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]

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

[Qliksenseranelx5]

Error in expression

[Qliksenseranelx5]

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

[Qliksenseranelx5]

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.