Subsets -- different calculations for a total field?????

Tue, 08 Jul 2014 17:23:29 GMT

I am attempting to use two different expressions within a subset of data. The expressions are to derive an adjusted Cost and Quantity for specified lines of data within the set. I am calculating cost and quantity, normally, by using the Cost and Quantity fields within my set. The adjusted calculations will need to reference other tables (“MaterialMaster” and “ComponentSubset”).

Some problems I’ve come across are due to the 1 to Many relationships I have in my ComponentSubset table. Each Sub Material can but does not always have more than one Parent Material. Additionally, each Parent Material will have at least 2 Sub Materials associated but can have 3 in some cases.

For all (Parent) Materials in my dataset that equal a Parent material in my ComponetSubset table, the following formula is needed to calculate “Adjusted Cost”:

Adjusted Cost = Parent Qty * (Sub1 Standard Cost + Sub2 Standard Cost… + Sub3 Standard Cost (where necessary))
- **Standard Cost can be found in the Material Master table via the MaterialPlant field.

For all (Sub) Materials in my dataset that equal a Sub material in my ComponentSubet table, the following formula is needed to Calculate “Adjusted Quantity”:

Adjusted Quantity = Sub Quantity – Parent1 Quantity… - Parent2 Quantity (where necessary)

Ultimately, I am hoping to blend the normal Cost and Quantity calculation (reference Fields within dataset) with the adjusted calculations.

All materials not included in the Parent field of the ComponentSubset table will use the Cost field from the dataset and all materials found in the Parent field will use the Adjusted Cost calculation to create a Total Cost field.
All materials NOT included in the Sub field of the ComponentSubset table will use the Quantity field from the dataset and all materials found in the Sub field will use the Adjusted Quantity calculation to create a Total Quantity field.

For example, to calculate the Adjusted Cost of (Parent) Material - 59012 and Adjusted Quantity of (Sub) Material – 59004:

Parent Qty                          = 15
Sub1 Standard Cost         = 55
Sub2 Standard Cost         = 95
Adjusted Cost - 2250      = 15 * (55 + 95)

Sub Quantity                      = 40
Parent1 Qty                        = 15
Parent2 Qty                        = 15

Adjusted Quantity – 10 = 40 – 15 – 15

I have attempted many different designs to accomplish these calculations but have not been able to complete everything I’ve needed yet. Originally, I tried using the aggr function to aggregate by Parent and/or Sub where applicable but could not figure it out completely. I ha !ve since attempted to create temporary tables in the script, yet still have not been able to figure it out completey. Attached is a qvw and an excel file with all data needed to simulate what I need. Can anyone help, please??!!??!!

topic Subsets -- different calculations for a total field????? in QlikView

Subsets -- different calculations for a total field?????