Count help

[#1]

Hi All,

I have data something like this

Acct_Prod:
LOAD * INLINE [
Acct, Product
Acct_A, Prod_A
Acct_A, Prod_B
Acct_B, Prod_A
Acct_B, Prod_B
Acct_C, Prod_B
Acct_C, Prod_C
];

want to calculate Avg products per purchasing Acct

the total number of Products purchased by purchasing Accts / total number of unique purchasing Accts

E.g.


Acct Acct_A purchased Prod_A, Prod_B.
Acct Acct_B purchased Prod_A and Prod_B, and
Acct Acct_C purchased Prod_B and Prod_C.

For Prod_A:

Total number of products purchased = 4 (Prod_A, Prod_B (for Acct_A), Prod_A, Prod_B (for Acct_B))
Total number of purchasing Accts 2. (Acct_A, Acct_B)

Avg products per purchasing Acct = 4/2.

Thanks in Advance

[chris_johnson]

[Removed - duplicated the post - sorry!]

[chris_johnson]

Hmm. My answer seems to work in a text box but not when put into a chart. I'm hoping someone can explain why and finish the job!

Chris

[#4]

yes.. I want it for chart...

[#5 · Accepted solution]

here is the solution

=sum( aggr(nodistinct count({1} distinct [Product]),ACCT))