Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

1 Solution

Accepted Solutions
vkish16161
Creator III
Creator III

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

*/

View solution in original post

9 Replies
sunny_talwar

Try this

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

Not applicable
Author

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

vkish16161
Creator III
Creator III

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
Creator III
Creator III

Sunny, you might have missed ')'

Count([TOPIC LEVEL 1])

Not applicable
Author

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
Creator III
Creator III

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

*/

sunny_talwar

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

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

Not applicable
Author

This worked, thank you so much!

sstefancies
Contributor III
Contributor III

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?