Qlik Community

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

ProductColor.costQuant.costValue CostTotal Value Cost
Prod 10Red10150,001.500,00
Prod 10Blue5100,00500,00
Prod 20Yellow375,00375,00
Prod 30Red3120,00360,00

Sold Table  

ProductColorQuantValue SaleTotal Value  Sale
Prod 10Blue3300,00900,00
Prod 20Yellow1150,00150,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
hector_munoz_in
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,

Really tks for your help.

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

Regards

kkkumar82
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

martinpohl
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:

LOAD * INLINE [

    ProductColor, Valued_Cost

    10Red, 150

    10Blue, 100

    20Yellow, 75

    30Red, 120

];

SALES:

LOAD * INLINE [

    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]

 

ProductColorEXT_SALEMargin $Margin %
Total$1,050.00$675.00-
10Blue$900.00$600.000.666666667
20Yellow$150.00$75.000.5
hector_munoz_in
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.