Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I need an help with Aggr count, I have attached a sample & explained my requirement.
Thanks In Advance
This seems to do the trick.
sum(aggr(count({<"BRAND ID"=>} total <"ACCT"> distinct "BRAND ID"),"BRAND ID","ACCT"))
Edit: Added set analysis to fix bug where if you selected a single row, the value changed.
View solution in original post
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.....
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.
Any help please....
=sum(aggr(count({1} distinct ACCT),[BRAND ID]))
- Ralf
its not working....
Same result as in your table above! The COUNT is also correct (in table above is wrong).
I want an expression for "# Brands Purchased BY these accounts ". Please see attached image... hope you understand my question...
This makes no sense. Brands purchased by WHICH accounts?
Btw. the dimension is Brand not Account!
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.
