Aggr Count Help

[#1]

Hi all,

I need an help with Aggr count, I have attached a sample & explained my requirement.

Thanks In Advance

[#2]

Your example doesn't really make a lot of sense.  You already have the answer for the first column and I'm not sure how you are supposed to get 2 7's, a 5, and a 2 with those 10 rows loaded for the second column.....

[#3]

BRAND 2 is used by :

- ACCT A

- ACCT B (two times)

- ACCT C

ACCT A uses BRAND 1 and BRAND 2 : 2 brands

ACCT B uses BRAND 1 and BRAND 2 and BRAND 3 : 3 brands. Even if ACCT B uses 2 times BRAND 2, it have to be counted only 1 time.

ACCT C uses BRAND 2 and BRAND 3 : 2 brands

So, BRAND 2 is used by ACCT that use 2+3+2 = 7 brands.

This in an interesting problem...

For me, the "simpliest" solution is to calculate the expected values in the load script, creating specific tables / columns.

[#4]

Any help please....

[rbecher]

=sum(aggr(count({1} distinct ACCT),[BRAND ID]))

- Ralf

[#6]

its not working....

[rbecher]

Same result as in your table above! The COUNT is also correct (in table above is wrong).

[#8]

I want an expression for "# Brands Purchased BY these accounts ". Please see attached image... hope you understand my question...

[rbecher]

This makes no sense. Brands purchased by WHICH accounts?

Btw. the dimension is Brand not Account!

[#10]

I clearly mentioned requirement in my attached files and also please look @ nmartin's reply...

Eg: for "BRAND 1"

# Accts bought "Brand 1" are 2 (Accts: A & B).

# Brands also bought (including Brand 1) by these accts are 5 (Distinct Brands from Acct A = 2 + Distinct Brands from Acct B = 3)

Note_1: Acct B bought BRAND 2 twice but It should consider it only once. i.e distinct brands by acct.

Note_2: User also have option to select Acct as Dimension, in that case how it is handled.