Bill of material explosion (BOM) - Qlik Community

Report Inappropriate Content · ‎2012-05-19

Hi everyone

It was requested of me to build a report that calculates the current cost of a specific product.

Let's call this product. Product A. Product A is the Father Product in this case.

Product A consists of two components, Product B & C. The quantity required of each component

to produce Product A is shown in the Qty field. The cost per unit of each component is shown in Unit Cost field.

FatherCode	FatherName	ChildCode	ChildName	Qty	Unit Cost
F00014829	Product A	HZA000991	Product B	29.44
F00014829	Product A	PZA00095	Product C	1.00	56.27

The issue I am having is that Product B is a Father Product on its own as well. There is a number of components required to produce Product B.

FatherCode	FatherName	ChildCode	ChildName	Qty	Unit Cost
HZA000991	Product B	R00025457	Component B1	0.00	691.12
HZA000991	Product B	R00027720	Component B2	0.19	32.59
HZA000991	Product B	R00032427	Component B3	0.42	9.23
HZA000991	Product B	R00033164	Component B4	0.08	33.84
HZA000991	Product B	R00034256	Component B5	0.15	4.99
HZA000991	Product B	R00036131	Component B6	0.05	42.08
HZA000991	Product B	R00039869	Component B7	0.00	233.78
HZA000991	Product B	R00056138	Component B8	0.10	39.00
HZA000991	Product B	R00084788	Component B9	0.00	76.69
HZA000991	Product B	R00098034	Component B10	0.00	67.46
HZA000991	Product B	R01038421	Component B11	0.00	170.63

What I want at the end is to select Product A. Qlikview should then calculate how much it will cost to produce Product A. Qlikview should automatically calculate how much it will cost to produce one unit of product B. This cost of product B should then be added to the Product B (as a component of Product A).

The total cost of one unit of product A should be 660.65. This is the correct answer.

Please find attached excel sheet. This excel sheet is an extract of the database we are using. I think the hierarchy function should be use in some way, but I am not sure how.

Report Inappropriate Content · ‎2012-05-21

Has anyone got a solution for this?

Report Inappropriate Content · ‎2012-05-21

Is there a way to tell which are finished goods?

Can you have more than 2 levels(Component B1 is also made of ...)?

Report Inappropriate Content · ‎2012-05-21

There you go:

Temp:

LOAD FatherCode,

FatherName,

ChildCode,

ChildName,

Qty,

[Unit Cost]

FROM

[BOM Cost.xls]

(biff, embedded labels, table is Sheet1$) ;

left join

LOAD FatherCode as ChildCode,

sum(Qty*[Unit Cost]) as [Unit Cost Child]

FROM

[BOM Cost.xls]

(biff, embedded labels, table is Sheet1$) group by FatherCode;

Final:

load FatherCode,FatherName,ChildCode,ChildName,Qty,if(isnull([Unit Cost]),[Unit Cost Child],[Unit Cost]) as [Unit Cost] resident Temp;

drop table Temp;

Report Inappropriate Content · ‎2012-05-21

Bobacaty

Just to answer your questions:

Is there a way to tell which are finished goods? The Finished Goods start with the letter "F"

Can you have more than 2 levels(Component B1 is also made of ...)? Yes you can have more than two levels.

The above script is working, but when I drill down into the Finished Product (Product A), I only get the cost of product B & C, I do not get the breakdown of product B. I am using a pivot table to see the explosion. For me to see the breakdown of Product B, I have to select product B as a Father.

Are there a way around this to be albe to start at level one and drill down to lets say level 5?

Thanks for your help so far.

Report Inappropriate Content · ‎2012-05-21

In this case you would have to build the 5 levels starting from finished goods. First you only load the products starting with 'F' and their ChildCode as ChildCode1. Then you load the ChildCode1 products that were previously loaded (where exists(ChildCode1,FatherCode)) renaming FatherCode to ChildCode1 and ChildCode to ChildCode2...

And so on....until you reach the bottom level. Your data model should have 1-N tables linked by theChildCodeN.

Be careful to also rename the Qty and Unit Cost to the same Qty1....(I bet your link between the tables is not only the product code but also the LotNo...)

Then in the chart you would have to build a Drill Down group with the Products and in the expression get the level you are on and put the coresponding QtyN...

It is a complicated calculation and you have to make sure that one of your childcodes is not part of more than one FatherCode. Then you would have to split the total ChildCost depending on the Father's qty...

Why do you have nulls in your table?Shouldn't the ERP distribute the cost?

Report Inappropriate Content · ‎2012-05-21

Hi Bobocaty

I will try the above solution you mentioned.

However, I do have several Childcodes that form part of more than on Fathercode. The cost should be based on the qty required to produce 1 unit of a father.

The cost I am using is the last purchase cost, we do not purchase finished goods, as we produce them ourselves. This is why only the components have a cost. What if one of the father codes do have a cost allocated to it (we maybe had to import the finished product), how would I ignore this, and rather use the cost of the children?

Could I maybe email you the full QVD file, for you to understand the complexity?

Report Inappropriate Content · ‎2012-05-21

Hi Chris,

You can email me the qvd(bobocaty@yahoo.com). Are you sure that this is the way to get the correct cost? In production you have more than the cost of the products that are part of the finished good. You have fixed costs(electricity, employees costs,amortization...) that you cannot include into the real cost of the final product(that's why maybe there is another table in your ERP that has the recipe and the real costs distributed).