Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Community,
I have a problem: I have a pivot table where the Dimensions are the following (in order):
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!
Sum( {1< item_cd = P({<doc_type={"Purchase"}, doc_status = {"OPEN"}>}[mainrawmat_cd])>} [quantity - deliv_qt] )
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.
Thank you for the replies, but they still don't fix my problem...
I'll give an example:
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!