Show fields by conditions at table as dimension

[coloful_architect]

I try to build a set expression with aggr and only to show a list by conditions at dimension at a table chart.

The table has Pack Units, Product Category, Business Name, Manufacturer.

business name is like a customer who could have multiple suppliers of Manufacturers. Trying to filter out a list with these conditions:

1. pack units >0

2. product category does not have other

3.  the business name( customer) has at least more than 1 manufacturer

I tried this ...the formula syntax seems good but does not return a correct result like the customer it returns still has Other.

any clue? 

=aggr(only({<[Pack Units]={">0"},[Product Category]=-{"Other"},

[Business Name]={"=count(distinct

[Manufacturer]) > 1"}>} [Business Name]),[Business Name])

[Lauri]

Try adding your set analysis filters to the Aggr function, like 

Aggr({<[Pack Units]={">0"},[Product Category]=-{"Other"}>} Only(...

[coloful_architect]

Hi Lauri,

Thanks for your suggestions. 

my version actually has aggr...(see my post above.) and now it runs ok. but if I want to add one more condition on pack unit >0 with like past 12 months , how I achieve it ?

[Lauri]

Do you have a date field in your table with [pack unit] that you can use? You can look at the past 12 months on a field called [Date] like this:

[Date]={">$(=AddMonths(Today(), -12))"}

[coloful_architect]

yeah ,but how I can emended into that pack units >0 and carry that into aggr function?

let's say my time dimension field is 6 month. 6 months sales is

sum(<{time={"6month"}}>[Pack Units])

how I can insert into the set expression below?  

aggr(only({<[Pack Units]={">0"},[Product Category]=-{"Other"},

[Business Name]={"=count(distinct

[Manufacturer]) > 1"}>} [Business Name]),[Business Name])

[Lauri]

Try this:

aggr(only({<time={"6month"}, [Pack Units]={">0"},[Product Category]=-{"Other"},

[Business Name]={"=count(distinct [Manufacturer]) > 1"}>} [Business Name]),[Business Name])

[coloful_architect]

awesome....one more condition if you dont mind. Now on top "[Business Name]={"=count(distinct [Manufacturer]) > 1" , I need to exclude two business companies A and B, how I do to insert it into business name expression.

please advise.

much appreciated

[Lauri · Accepted solution]

[Business Name]={"=count(distinct [Manufacturer]) > 1"}-{'A', 'B'}