What other fields or attributes differentiate one product from the other?
No differential fields or attributes present in database. I shall elaborate further.
In database Part A sales in unit quantity in nos. is present. Part A is composed of Part X with 25 Liters pack & Part Y with 3Liters pack. Now i want calculate total quantity in liters for both X & Y separately. So i want create Part A as say Part AX & Part AY so that i can calculate total liters for Part AX & Part AY i.e. for Part AX = 25*quantity of Part A & Part AY = 3*quantity of Part A.
I need to see some data to understand what the issue is. Can you post a dummy packaged workbook with sample data?
Yes i can provide packaged workbook attached here. In this Part 1, Part 2 & Part 3 are the entries with date & sales quantity is present in oracle database. Now Part 1, 2 & 3 are composed of two different type of chemical for e.g. Part 1 have one 25Liters A type of product & one 3Liters B type of product now want to calculate total A type of product in liters & total B type of product in liters. How shall i do this??
Hope you have 2018 installed. Is it possible for you to attach 10.5 or lower version?
No in my data the Sub-Category is not present. As i mentioned earlier Part 1, 2 & 3 are present in database but Product A & B (i.e Sub-Category) is not present. Part 1 sales is in quantity while the Product A is of 25Liters & B is of 3Liters, assume Part 1 sales quantity is 10 then i want output for Product A in liters i.e. 10*25=250Liters & Product B in liters i.e. 10*3=30Liters.
Looking at your data, I have few questions. For example, 3 units of Part 1 product was sold on 07/03/2018.
What does a unit of Part 1 look like? Is it a package containing 25 litres of Part X and 3 litres of Part , all separately packed but combined into a single container?
If yes, does this mean that on 07/03/2018, there were 75 litres of Part X and 9 litres of Part Y products in the 3 units of Part 1 sold?
Do Part 1, Part 2 and Part 3 products each contain 25 litres of Part X and 3 litres of Part Y?
Assuming the answer to the questions above is yes, then you can calculate the individual parts for each part or do it as a combined field.
For example, you could do;
IF [Part No.] = 'Part 1' THEN 25*[Quantity] END
IF [Part No.] = 'Part 2' THEN 25*[Quantity] END
IF [Part No.] = 'Part 3' THEN 25*[Quantity] END
Do same for Part Y
IF [Part No.] = 'Part 1' THEN 3*[Quantity] END
IF [Part No.] = 'Part 2' THEN 3*[Quantity] END
IF [Part No.] = 'Part 3' THEN 3*[Quantity] END
However, you can just make two calculations and use [Part No.] as a filter to get the sub categories. See alternative codes below
Hope this helps.