Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Luis,
Try the following: (Sum([Sold Price] * Units) - Sum([Cost Price] * Units)) / Sum([Sold Price] * Units)
Regards,
H
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
I didn't get your problem here, what is the problem in the above solution given by Hector, can you brief a little bit
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
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]
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 |
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
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
Here''s a sample QVD.