Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ptwistp1
New Contributor

Count If or Sum If values "Contain" text in a line chart

Hello Community!  this is my first question/post, so I am super excited LOL!

I have looked and looked but have not found the answer, so I felt it's finally time to break down and ask the question.

I am trying to create a line chart or a bar chart, either or is ok with time series data and strings of text. The issue is that not all of my data values are equal.  A common string of text looks like this ("Shut down AV system left on after meeting").  I would like to filter only on data that contains 'left on' and then count how many times I see this "string" of text per month so we can trend/track a reduction in cases that we have to dispatch a technician to a site to turn off the audio-visual equipment.

I have tried the following --> =count(if(match(close_notes, 'left on'),1,0)) but this is not giving me the return that i am looking for when i put this formula in the "measures" "label" formula section. It's actually giving me just the normal count of "close notes" the data set has

QlickSense-Image 1.png


PLEASE HELP! and ++ Karma points if it works

4 Replies
ptwistp1
New Contributor

Re: Count If or Sum If values "Contain" text in a line chart

Well, of course after I post, I found the answer to my own question, sorry guys for the waste of time.

To resolve this I had to use "Sub_String" in the formula, for example --> =sum(if(SubStringCount(close_notes,'left on'),1,0))

Sorry in advance, but I fixed my own issues LOL!  Thanks anyway!!

Re: Count If or Sum If values "Contain" text in a line chart

I suggest using set analysis like this

Sum({<close_notes = {'*left on*'}>} close_notes)

ptwistp1
New Contributor

Re: Count If or Sum If values "Contain" text in a line chart

Thank you for the reply, the solution to my issue was -->  =sum(if(SubStringCount(close_notes,'left on'),1,0))

Re: Count If or Sum If values "Contain" text in a line chart

Did you even try with set analysis my friend?

Community Browser