Sum over unique rows in expression

[#1]

Hi all,

I want to calculate the percentage of stock that is obsolete in a gauge chart. I have two tables, one with the stock levels and one with sales.

The stock table looks as follows:

product  stock    obsolete

A           300       YES

B           200        NO

C           200        NO

D           300        NO

I also have the following sales table:

product   sold

B           20

B           30

B           45

C           10

C           100

D           150

When I now calculate the sum in a graph expression, it contains many duplicates, I presume that this is the case due to Qlikview making the one-to-many relation between these tables (as expected in the rest of the dashboard, but not what I want here..).

The result here should be the sum of obsolete stock, divided by the total stock. In this case: 300/(300+200+200+300) = 30%.

I have been messing around with aggregate, total, distinct, sum, min, but I cannot seem to get it right..

Could anybody help me or point me in the right direction?

Thanks in advance,

Florian

[Colin-Albert]

Great, glad you've fixed the problem

If you can mark any helpful and correct answers to close the post this will help others searching on a similar issue.