topic Re: Set Analysis Count Excluded Problem in QlikView

Set Analysis Count Excluded Problem

FVenthakone — Tue, 04 Dec 2012 12:48:51 GMT

Hi,

I’ve been having some trouble with an expression using set analysis.

I have a list of distributors and their resellers for each YearWeek.

In a pivot table, when a distributor is selected, I need to count the exclusive Resellers of the selected Distributor. So with YearWeek as a dimension, I tried counting Reseller and then subtracting the ones in common with the excluded:

Count(DISTINCT Reseller) - Count({<Reseller= P({<Distributor=E(Distributor)>})>} DISTINCT Reseller)

However, this is not giving the correct count for each YearWeek. I only get the right count for a YearWeek when I select it.

Any thoughts?

Thanks

Re: Set Analysis Count Excluded Problem

swuehl — Tue, 04 Dec 2012 13:21:45 GMT

Felix,

the set analysis will only be evaluated once per chart, it will not consider your dimension values.

Could you upload a small sample that demonstrate your issue and setup?

Re: Set Analysis Count Excluded Problem

FVenthakone — Thu, 06 Dec 2012 15:10:33 GMT

Hi swuehl,

Thanks for your reply. I've attached an example qvw.

When I select a Distributor e.g. Sweden, I want to get the count of resellers exclusive to Sweden. For W1-10, the count of exclusive resellers for Sweden is 58, but in the pivot table I get 10. I only get 58 when I select W1-10 as YearWeek.

Thanks

Re: Set Analysis Count Excluded Problem

swuehl — Thu, 06 Dec 2012 15:49:10 GMT

Maybe along these lines:

=sum(aggr(if(count({<Distributor>} distinct Distributor)=1,1), YearWeek, Reseller))

Re: Set Analysis Count Excluded Problem

FVenthakone — Tue, 11 Dec 2012 09:37:24 GMT

Thanks swuehl, that's exactly what I wanted.