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!

[#2]

Please provide some sample data!

Vinay

[#3]

HI,

Not sure why you need set analysis here. Just sum(Order Qty) should work. Is there any reason why you want to use Set analysis?

[mphekin12]

If I use sum(Order Qty) it only gives me the sum of the Order Qty for the Parent Product ID.  I need this sum, as well as the sum for all of the components.

[mphekin12]

Here is a small sample.  I hope this explains my situation a little better.

Any help would be greatly appreciated!

[srchilukoori]

The order Qty data is associated with the Product ID in the Test.qvw

When you use Sum(OrderQty) you are in fact aggregating the data at the Product ID level.

[mphekin12]

srchilukoori,

Thanks for your reply.  Maybe I'm not explaining the problem correctly.  If you open the sample application and select ProductID MFLIDPNL17X1012GAHRF&F you will see that there are 3 components to that Product.  What I need is the Usage for that Product as well as the Usage for the 3 components.  In the Pivot table in the sample app, the Usage displayed is only for the Parent Product ID.

The sample size I provided was very small, so it would be easy to figure out, but when you have well over a million products it becomes more challenging.

Thank you!

[srchilukoori]

use this expression:

=Sum(Total <ProductID> OrderQty)

[mphekin12]

srchilukoori,

That formula doesn't work.  I opened up the Test.qvw and it looks like you changed the data model, which isn't going to work in my case.

Thanks for all of your help!

[srchilukoori]

In your sample data,

Products:

  LOAD ProductID,

      [Product Description]

  FROM

  Book1.xlsx

  (ooxml, embedded labels, table is Product);

WorkOrder:

  LOAD ProductID,

            OrderQty,           ========> It doesn't link Component ID with Order Qty (Results in cartesian product)

            Cost

  FROM

  Book1.xlsx

  (ooxml, embedded labels, table is WO);

Components:

LOAD ProductID,

     ComponentID

FROM

Book1.xlsx

(ooxml, embedded labels, table is Component);

--------------------------------------------------------------------------------------------------------------------------------------------------------------

The model I used links  Order Qty with Product ID and Component ID, which is necessary to show the data at the product ID and Component ID levels.