Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Not applicable

A count of a count function?

Hello,

I want to make a KPI measure that counts all of my document titles located in 2 places. So I have started with this.

Capture.PNG

The measure counts how many different topics the asset is categorized under.

I now want a KPI to, in this case, read 5, because there are 5 asset titles with a Count(topic level 1) > 1.

How is this formatted?

Tags (3)
1 Solution

Accepted Solutions
vkish16161
Contributor III

Re: A count of a count function?

Possible solution:

Might need some work in the backend in separating "Name" from "Topic"

Sum (

if( Aggr( Count(Topics) , Names  )  > 1 , 1, 0 )

)

/*

Here Topics and Names are individual field names

*/

9 Replies

Re: A count of a count function?

Try this

Count(DISTINCT {<[ASSET TITLE] = {"=Count([TOPIC LEVEL 1] > 1"}>} [ASSET TITLE])

Not applicable

Re: A count of a count function?

It didn't work. It doesn't recognize [TOPIC LEVEL 1] As a field because it's inside the ""

vkish16161
Contributor III

Re: A count of a count function?

Could you be more specific please?

Maybe you could try something like this..

Sum(

if    (

Aggr ( Count(topic level 1) , ASSET TITLE   )

>1 , 1 ,0 )

vkish16161
Contributor III

Re: A count of a count function?

Sunny, you might have missed ')'

Count([TOPIC LEVEL 1])

Not applicable

Re: A count of a count function?

You are all on the right track, but my numbers keep coming out to 0. If an asset is part of 2 different topics, it is listed as

'Name 1 - Topic 1'

'Name 1 - Topic 2'

etc

So I want to count all those asset names that are listed under MORE THAN 1 topic. Topic Level 1 is a field as well as asset title. Hope that helps

vkish16161
Contributor III

Re: A count of a count function?

Possible solution:

Might need some work in the backend in separating "Name" from "Topic"

Sum (

if( Aggr( Count(Topics) , Names  )  > 1 , 1, 0 )

)

/*

Here Topics and Names are individual field names

*/

Re: A count of a count function?

You are right... forgot to add ')'

=Count(DISTINCT {<[ASSET TITLE] = {"=Count([TOPIC LEVEL 1]) > 1"}>} [ASSET TITLE])

Not applicable

Re: A count of a count function?

This worked, thank you so much!

sstefancies
New Contributor III

Re: A count of a count function?

I used this solution to count jobs that have fewer than a specific type of activity on them.  It almost worked.  In this example, it is looking for >1, I am looking for <2.  It is ignoring 0s and only counting 1s.  This is my expression:

 

Sum (

if( Aggr( Count( {<[jc.act-code]={'W','W1'}>} [jc.act-code]) , [wo-no.disp-no] ) < 2 , 1, 0 )

)

 

A sample of my data:

Count of Activities, wo-no.disp-no

8, 1111

1, 2222

0, 3333

That expression counts 1 with that data.  I want it to count 2.

How can I make the zeroes  get counted?