use only and count at dimension to show selective fields

[coloful_architect]

Hi community,

I am trying to build a plain table with fields Business, Units , and suppliers.

one business could have multiple suppliers.

at the table I want to only show business with suppliers larger than 1. that means I dont want to have business with sole supplier.

here is the set expression at dimension of business. for some reasons , it does not work out. any ideas?

=aggr(only({count(<distinct

Supplier)={">1"}>} [Business Name]),[Business Name])

[stevejoyce · Accepted solution]

Some syntax issues there.  Try below:

=aggr(only({<[Business Name]={"=count(distinct Supplier) > 1"} >} [Business Name]),[Business Name])

[coloful_architect]

thanks Steve,

what if I want to add one more set conditions for units % by suppliers < 60%, I tried this, but no luck to get what I want

=aggr(only({<[Business Name]={"=count(distinct Supplier) > 1"},

(max(aggr(sum([Pack Units]),[Business Name],Supplier))

/
sum([Pack Units]))={"<60%"}>

} [Business Name]),[Business Name])

[coloful_architect]

Hi Steve,

what if I want to add one more condition on top of [Business Name]={"=count(distinct Supplier) > 1 to exclude business A and B?

tried

[stevejoyce]

Try:

aggr(only({<[Business Name]={"=count(distinct {<[Business Name] -= {'A','B'}>} Supplier) > 1"} >} [Business Name]),[Business Name])

[coloful_architect]

This is really helpful. Bear with me for one more condition to filter out only those business name whose sales in 6 months larger than 0.

dimension of sales is [Sales] and let's say 6 month field is [6 months]

much appreciated