topic Count help in QlikView

Count help

Mon, 25 Apr 2011 21:37:04 GMT

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

Count help

erichshiino — Mon, 25 Apr 2011 22:02:24 GMT

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

Total number of products purchased = 4 ?

Count help

Mon, 25 Apr 2011 22:13:14 GMT

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

Tue, 26 Apr 2011 04:22:36 GMT

Hi,

Not sure i have understood your question.

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

- Sridhar

Count help

Tue, 26 Apr 2011 16:09:43 GMT

Nope its not working... any help

Count help

Tue, 26 Apr 2011 16:17:54 GMT

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

Count help

chris_johnson — Tue, 26 Apr 2011 16:46:41 GMT

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

Tue, 26 Apr 2011 16:48:59 GMT

yes exactly..

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

Count help

chris_johnson — Tue, 26 Apr 2011 16:57:37 GMT

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

chris_johnson — Tue, 26 Apr 2011 17:01:36 GMT

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

chris_johnson — Tue, 26 Apr 2011 17:02:44 GMT

[Removed - duplicated the post - sorry!]

Count help

chris_johnson — Tue, 26 Apr 2011 17:05:39 GMT

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

Tue, 26 Apr 2011 17:07:48 GMT

yes.. I want it for chart...

Count help

Tue, 26 Apr 2011 19:46:06 GMT

here is the solution

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