topic Count distinct with set analysis or aggr function in Water Cooler

Count distinct with set analysis or aggr function

Anonymous — Wed, 14 Nov 2018 21:34:26 GMT

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

Re: Count distinct with set analysis or aggr function

Oleg_Troyansky — Wed, 14 Nov 2018 21:48:21 GMT

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

Re: Count distinct with set analysis or aggr function

Anonymous — Wed, 14 Nov 2018 21:56:14 GMT

Thanks Oleg, worked like a charm!