Sum Conditional Set

[Anonymous]

Hello Community,

I have a problem: I have a pivot table where the Dimensions are the following (in order):

• Main Component (mainrawmat_cd)
• Item (item_cd)

The report gets the information from a table where we have the movement of the items (purchase/sales/stock movement/etc).

Each row of the movement table has it's values according to the item, which could be the main component of other items.

My problem is that I want the report to have the quantity of items purchased for each Main Component, along with the quantity sold for each Item.

But the information table doesn't separate which items are Main Components. They are all Items. There is a column in the information table that shows the Main component of each item.

I tried the following expression:  Sum( {$<doc_type={"Purchase"}, doc_status = {"OPEN"}, item_cd = {mainrawmat_cd}>} ( quantity - deliv_qt ) )

But it's still wrong...

Is it ok to compare 2 dimensions in the conditions?

Is there anyway to cancel the intrinsic condition of each row (where it sums for item_cd) and show the sum of any given condition? (in this case, where item_cd = mainrawmat_cd )

Thank you for your help!

[er_mohit]

Sum( {1< item_cd = P({<doc_type={"Purchase"}, doc_status = {"OPEN"}>}[mainrawmat_cd])>} [quantity - deliv_qt] )

[Gysbert_Wassenaar]

If item_cd is the primary key of your table then this should work:

Sum( {$<doc_type={"Purchase"}, doc_status = {"OPEN"}, item_cd = {'=item_cd=mainrawmat_cd'}>} ( quantity - deliv_qt ) )

If it isn't you could try creating a new field in the script: recno() as rowid and use that new field in the expression

Sum( {$<doc_type={"Purchase"}, doc_status = {"OPEN"}, rowid = {'=item_cd=mainrawmat_cd'}>} ( quantity - deliv_qt ) )

Otherwise you'll have to use an if statement for the intra-record comparison.

[Anonymous]

Thank you for the replies, but they still don't fix my problem...

I'll give an example:

• Item_cd = 11111 and item_cd = 22222 have mainrawmat_cd = 55555.
• Item_cd = 55555 has mainrawmat_cd = 55555.
• Item_cd 11111 has been sold 5 different times, let's say quantity = 20 for each time/row.
• Item_cd 22222 has been sold 3 different times, let's say quantity = 15 for each time/row.
• Item_cd 55555 (which is item_cd 11111's and item_cd 22222's mainrawmat_cd) has been purchased 2 times, let's say quantity = 40 and deliv_qt = 10.
• *Remember these are 10 different rows in the information table.
• *Item_cd is the primary key for this pivot table. In other words, the information is displayed according to item_cd.

Now on the pivot table, in the same row, I'd like to see:

Mainrawmat_cd          item_cd      quantity sold(for item_cd)          quantity purchased(for mainrawmat_cd)

55555                        11111          100                                        60

55555                        22222          45                                          60

55555                        55555          0                                            60

Is it a little bit clearer how the information is arranged?

When I use the suggested solutions mentioned above, they only work if i have mainrawmat_cd 55555 and item_cd 55555 on the same row (3rd row in the example). The other rows remain 0.

Thank you for your help!