Discussion board where members can get started with Qlik Sense.
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"
|Product||Color.cost||Quant.cost||Value Cost||Total Value Cost|
|Product||Color||Quant||Value Sale||Total Value Sale|
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.
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.
Concatenate Product and Color to avoid synthetic keys.
LOAD * INLINE [
LOAD * INLINE [
ProductColor, Quant, Value_Sale, EXT_Sale
10Blue, 3, 100, 900
20Yellow, 1, 150, 150
Create a pivot table with the following expression:
Sales = sum(EXT_Sale)
Margin $ = sum(EXT_Sale-(Valued_Cost*Quant))
Margin % = =[Margin $] / [EXT_Sale]
|ProductColor||EXT_SALE||Margin $||Margin %|
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.
Munoz, Kiran and Martin,
Many thanks for all you help. All of them help my improvement.
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.