Set Analysis Count Excluded Problem

[FVenthakone]

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

[swuehl]

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?

[FVenthakone]

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

[swuehl · Accepted solution]

Maybe along these lines:

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

[FVenthakone]

Thanks swuehl, that's exactly what I wanted.