Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe along these lines:
=sum(aggr(if(count({<Distributor>} distinct Distributor)=1,1), YearWeek, Reseller))
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?
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
Maybe along these lines:
=sum(aggr(if(count({<Distributor>} distinct Distributor)=1,1), YearWeek, Reseller))
Thanks swuehl, that's exactly what I wanted.