Urgent: InfoSet Query Problem

[Former Member]

Hi:

I have a question related to the InfoSet issue I have been experiencing.

I have a SD ODS which has e.g Material, Cal Year/Month and Sales Qty, Sales Amt. I have another ODS which contains BOM (Bill Of Material Data) consists of Material, Components and Qty for each component. I created an InfoSet by using material as join field between this. When I created a query it shows report as shown below.

Material Component Sales Qty Sales Amt BOM Qty

111 AAA 1000 500 50

111 BBB 1000 500 20

111 CCC 1000 500 10

It means It keeps sales Qty for each component so at aggregated level my sales qty and amt is 3000 and 1500 resp. Which is not correct. What can I do in order for Sales Qty & Sales Amt not to repeat. Any help will be appreciated.

[Former Member · Accepted solution]

Hi,

One way to get your correct values is by using formulas for sales quantity ands sales amt:

sales qty formula = sales qty * BOM Qty / SUM(BOM Qty)

in this way you will get

111 AAA 625 312,5 50

111 BBB 250 125 20

111 CCC 125 75,5 10

Cioa

Joke