13 Replies Latest reply: Apr 26, 2011 3:46 PM by Anil Konduri

Count help

Hi All,

I have data something like this

Acct_Prod:
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.

• Count help

I'm sorry, but how do you get this result:

Total number of products purchased = 4 ?

• Count help

the above e.g is for Prod_A

Prod_A contains in 2 Acct's (Acct_A & Acct_B), so total number of products in those 2 accts is 4 (No distinct)

• Count help

Hi,

Not sure i have understood your question.

Check the attached application and let me the solution soulves your problem.

- Sridhar

• Count help

Nope its not working... any help

• Count help

It doesn't solve your prob`s is it? what is the problem..? could you pl be specific and clear?

• Count help

If I understand this correctly then, in your example you want to total up the number of products sold to a customers who have baught product A?

Chris

• Count help

yes exactly..

Also I have attached a sample how I want to show my chart.

• Count help

I'm half way there:

=count ({\$<Acct = (P({1<Product={'Prod_A'}>}))>} Product)

That will give you the number of products sold to accounts who have bought 'Prod_A' (I think! - I'm making it up as I go along!)

Chris

• Count help

Maybe this?

=count ({\$<Acct = (P({1<Product={'Prod_A'}>}))>} Product) /
count ({\$<Acct = (P({1<Product={'Prod_A'}>}))>} distinct Product)

I'd try it out though as I'm making it up using the example qvd from a few posts back!!

Chris

• Count help

[Removed - duplicated the post - sorry!]

• Count help

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

• Count help

yes.. I want it for chart...

• Count help

here is the solution

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