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

[erichshiino]

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

Total number of products purchased = 4 ?

[#3]

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)

[#4]

Hi,

Not sure i have understood your question.

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

- Sridhar

[#5]

Nope its not working... any help

[#6]

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

[chris_johnson]

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

[#8]

yes exactly..

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

[chris_johnson]

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

[chris_johnson]

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