Qlik Community

Community Corner

Discussion board for questions about the Qlik Community, its features, sharing information, general discussions and even some fun. This is for both new and longtime community members. Everyone is welcome!

Partner
Partner

Count distinct with set analysis or aggr function

Hi all,

I have a data table that has by workstream people and their associated travel expenses. I am trying to create a chart that shows the distinct count of these people (by name) by workstream for those that have more than 0 travel expenses. I tried this with an aggr function and set analysis but none of them are working. 

Can somebody help? The picture below shows what I am trying to do

Thanks in advance

2018-11-14_15-30-51.png

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Count distinct with set analysis or aggr function

In short, you need to apply Advanced Search in your Set Analysis. Something like this:

count( {<Name = {"=sum({<[Expense Category] = {'Travel & Expenses'} >} Amount)>0"}>}   distinct Name)

Advanced Search condition (look it up) is enclosed in double quotes and begins with the equal sign. After the equal sign, you can list any condition to be validated.

If you are interested, I'm teaching that and a lot more of Set Analysis and AGGR at the Masters Summit for Qlik - coming to Stockholm this April! Check it out!

Cheers,

Oleg Troyansky

2 Replies
MVP & Luminary
MVP & Luminary

Re: Count distinct with set analysis or aggr function

In short, you need to apply Advanced Search in your Set Analysis. Something like this:

count( {<Name = {"=sum({<[Expense Category] = {'Travel & Expenses'} >} Amount)>0"}>}   distinct Name)

Advanced Search condition (look it up) is enclosed in double quotes and begins with the equal sign. After the equal sign, you can list any condition to be validated.

If you are interested, I'm teaching that and a lot more of Set Analysis and AGGR at the Masters Summit for Qlik - coming to Stockholm this April! Check it out!

Cheers,

Oleg Troyansky

Partner
Partner

Re: Count distinct with set analysis or aggr function

Thanks Oleg, worked like a charm!