Set Analysis Question

[mphekin12]

Hello,

I have a question regarding set analysis.  I have a Products, a Work Order and a Component table.  The Products table contains information for all of our products.  The Work Order table contains sales stats.  Sometimes we build products by combining several products together.  The information of which products make up our built products are contained in the Components table.

Product Table

   ProductID

   ProductDescription

Work Order

     Product ID

     OrderQty

     Cost

Component

     ProductID

     ComponentID

I need to create a chart that will show me the Order Qty for a given Product as well as the components of the Product.

I have attached a screenshot of what the chart should look like.  I'm thinking that the Set Analysis logic would be something like

sum({1<ProductID={ComponentID}>} OrderQty) for all of the components, but I can't get the formual to work.

Any help would be greatly appreciated!

[srchilukoori]

However, the expression should still work.

[#3]

Can you rephrase : Usage for that Product as well as the Usage for the 3 components

When i added Product ID to that table i got the above result.

Which means per your data model this product has three different components but just one OrderQty.

Can you post an example of how you want the data to look like(in the resultant table)?

[javier_florian]

Do you try with aggr(Sum(Qty), Dim1)?

-JFlorian

[mphekin12]

Ajay,

I have attached another sample application.  If you look on the left hand side, it will show you what results I would like to see.

The table you posted shows the Usage for just the Product ID.  That product has three components.  Each of these components are in the Products and have records in the Work Orders table.  I need to see the Usage for these components.

Again, look at the attached application and see if it makes sense now.

Thank you!

[#6]

Now I fully understand. Attached with answer. Check the back-end, made a small change.

Let me know if you run into any issues.

Thanks

AJ

[mphekin12]

Ajay,

Thanks for you help!  Unfortunately I cannot change the data model.

[mphekin12 · Accepted solution]

I want to thank everyone for their help!

After some digging around the message board and the QlikView for Developers Cookbook I was able to come up with a solution using Set Analysis.  I am posting the final application for anyone interested in learning how I solved this problem.