Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

8 Replies
hector_munoz
Specialist
Specialist

Hi Luis,

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

Regards,

H

Not applicable
Author

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
Specialist III
Specialist III

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
Partner - Master
Partner - Master

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
Author

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
Specialist
Specialist

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
Author

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
Author

Here''s a sample QVD.