Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count help

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

1 Solution

Accepted Solutions
Not applicable
Author

here is the solution

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

View solution in original post

13 Replies
erichshiino
Partner - Master
Partner - Master

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

Total number of products purchased = 4 ?

Not applicable
Author

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)

Not applicable
Author

Hi,

Not sure i have understood your question.

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

- Sridhar Yes

Not applicable
Author

Nope its not working... any help

Not applicable
Author

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

chris_johnson
Creator III
Creator III

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

Not applicable
Author

yes exactly..

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

chris_johnson
Creator III
Creator III

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
Creator III
Creator III

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