topic Re: Count number of string appearances in QlikView

Count number of string appearances

Tue, 02 Feb 2016 13:33:03 GMT

Hi there,

I have a data set like below.

I would like to count the number of times that RED appears and use that as the expression on a Guage chart.

Can any one help?

Task ID	RAG Status
1	RED
2	GREEN
3	GREEN

Re: Count number of string appearances

sunny_talwar — Tue, 02 Feb 2016 16:51:21 GMT

Try this:

To count Red

=SubStringCount(Concat([RAG Status], '|'), 'RED')

To count Green

=SubStringCount(Concat([RAG Status], '|'), 'Green')

=Count({<[RAG Status] = {'RED'}>}[RAG Status])

=Count({<[RAG Status] = {'GREEN'}>}[RAG Status])

Re: Count number of string appearances

Thu, 04 Feb 2016 07:38:36 GMT

Hi There,

This doesn't seem to have worked unfortunately, the count is showing as the total number of rows in the table, rather than the number of instances where the value is RED

Count({<[RAG Status] = {"RED"}>}[Rag Status])

Thanks

Re: Count number of string appearances

sunny_talwar — Thu, 04 Feb 2016 18:21:39 GMT

I guess would you be able to share a application where it isn't working?

Re: Count number of string appearances

HirisH_V7 — Thu, 04 Feb 2016 19:31:51 GMT

Hi ,

Change your Expression like this,

May be its ,

Count({<[Rag Status] = {"RED"}>}[Rag Status])

Count({<[RAG Status] = {"RED"}>}[RAG Status])

Re: Count number of string appearances

Fri, 05 Feb 2016 10:05:37 GMT

Can you help me a little bit here too:

So I've just added an if condition.

I want to count the number of RAG status where the status is open.

This doesn't seem to work - to confirm, the count now does work. Just need the if to work around it.

if([Status]='Open', Count({<[RAG Status] = {"RED"}>}[RAG Status]), null())

Re: Count number of string appearances

HirisH_V7 — Fri, 05 Feb 2016 17:54:37 GMT

Hi,

if([Status]='Open', Count({<[RAG Status] = {"RED"}>}[RAG Status]), null())

if([Status]='Open', Count({<[Status]={'Open'},[RAG Status] = {"RED"}>}[RAG Status]), null())

Will works Fine.In Your Table you should Suppress Null values at Presentation and Dimensions tab as well.

If your dimension is status means there you can see only Open as a field.

HTH,

Hirish