Count distinct with set analysis or aggr function

[Anonymous]

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

[Oleg_Troyansky · Accepted solution]

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

[Anonymous]

Thanks Oleg, worked like a charm!