# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

## Margin of sold itens

Hello people,

Need help.

I have two tables (Cost Table and Sold Table) as below, and I need to calculate the margin of the sold products.

The tables are linked by "Product"

Cost Table

 Product Color.cost Quant.cost Value Cost Total Value Cost Prod 10 Red 10 150,00 1.500,00 Prod 10 Blue 5 100,00 500,00 Prod 20 Yellow 3 75,00 375,00 Prod 30 Red 3 120,00 360,00

Sold Table

 Product Color Quant Value Sale Total Value  Sale Prod 10 Blue 3 300,00 900,00 Prod 20 Yellow 1 150,00 150,00

The correct margin for the sold products is

Total Value Sale = 1.050 (3 x 300 + 1 x 150)

Total Value Cost = 375  (3 x 100 + 1 x 75)

Margin = 0,6429 or 64,29%

But I can't find a formula to get those number

Hope someone can help me.

Cheers

Luis

Tags (1)
8 Replies
Valued Contributor

## Re: Margin of sold itens

Hi Luis,

Try the following: (Sum([Sold Price] * Units) - Sum([Cost Price] * Units)) / Sum([Sold Price] * Units)

Regards,

H

Not applicable

## Re: Margin of sold itens

Munoz,

The problem is the price is different for different colors (the product code are equal), and the tables are linked only by products

Regards

Valued Contributor III

## Re: Margin of sold itens

I didn't get your problem here, what is the problem in the above solution given by Hector, can you brief a little bit

Valued Contributor II

## Re: Margin of sold itens

If the link between the tables is product + color you have to links the tables with BOTH fields.

If you do this by the fields you will get a sync table, this is not so good in Qlik.

So generate a field product & color in both tables. And for the selection let the explicit product and color fields only in the cost table.

Regards

Not applicable

## Re: Margin of sold itens

Try this:

Concatenate Product and Color to avoid synthetic keys.

COST:

ProductColor, Valued_Cost

10Red, 150

10Blue, 100

20Yellow, 75

30Red, 120

];

SALES:

ProductColor, Quant, Value_Sale, EXT_Sale

10Blue, 3, 100, 900

20Yellow, 1, 150, 150

];

Create a pivot table with the following expression:

Dimension: ProductColor

Expressions

Sales = sum(EXT_Sale)

Margin \$ = sum(EXT_Sale-(Valued_Cost*Quant))

Margin % = =[Margin \$] / [EXT_Sale]

 ProductColor EXT_SALE Margin \$ Margin % Total \$1,050.00 \$675.00 - 10Blue \$900.00 \$600.00 0.666666667 20Yellow \$150.00 \$75.00 0.5
Valued Contributor

## Re: Margin of sold itens

I agree with Martin... You have to choose the proper field or fields to associate tables in QlikView models in order to obtain the desired results in objects.

Regards,

H

Not applicable

## Re: Margin of sold itens

Munoz, Kiran and Martin,

Many thanks for all you help. All of them help my improvement.

Lyndon,

You answer are a little bit above my knowledge buy I will try hard to understand and implement ii..  I will confirm asap if it work or not for me.

Really thanks for you help.

Cheers

Luis

Not applicable

## Re: Margin of sold itens

Here''s a sample QVD.

Community Browser