Set analysis code (if)

[Qlikuser09]

i am looking for a set analysis code to convert (if there are more than 1 id Count for a specific field (product) then divide 1 by that count of id something like (1/n) where n is the count of id.

Customer ID	Products 1
1	Pen
2	pencil
3	book
4	Eraser
5	Pen
6	pencil
7	book
8	pen
8	pencil
9	Book
9 9	Eraser Eraser
10	Pencil
10	Book

Here the count if ID - (10) is 2  so the result will be 1/2=0.5, for id 9 it will be 1/3=0.33

[AshutoshBhumkar · Accepted solution]

Hi,

The result can be derived by Count (Distinct Customer ID) / Count (Customer ID)

[Qlikuser09]

Set analysis code for "Count of Products for Distinct Customer ID" will that work?

[AshutoshBhumkar]

Hi, May I please know what output you are expecting?

[Vegar]

If @AshutoshBhumkar  solution  isn't enough to solve your problem, then you probably need to re-explain your issue in other words  in order for us to help you. 

[Qlikuser09]

Customer ID	Products	Calculated Count
		0.333
9	Pen	1
9	Paper	1
9	Book	1

This is the result I am getting also the bar graph shows 1, 1, 1 for each product, which in turn gives the wrong Customer ID count. I am not getting the 0.33 value for each product. Also if I put a count  (CustomerID) the result displays as 1 and not as 3, but when I try putting it in the chart the result gets split.

[Vegar · Accepted solution]

An expression always try to  split up the result per dimension combination in a chart. If you add Product and Customer  ID then it will calculate the count per Product and  Customer ID. 

You can override this behaviour by defining your aggregated scope using TOTAL. 

Try an expression like this:

Count (Distinct Customer ID) / Count (TOTAL <[Customer ID]> Customer ID) 

[Qlikuser09]

Thanks this worked for me in table chart!! however,  I added this to the bar chart, the value still shows 1 for each product.