I'm having a problem trying to compare two rows of data. I'm trying to compare two lines of data with the same order number and item number since the information automatically aggregates. Esentially, my report is compiling information by Sales person, Order number, and Item number. The whole report is filtered by the date. Here is what my Webi report looks like.
*Sales Person CustomerOrder Number Item NumberOrig QtyUnit PriceSales $ Qty ReturnedCred $ Net*
GRI 002 BLU001 ORD000000219 R408A24 80 220 17600 40 8800 8800
GRI 002 BLU001 ORD000000219 R409A30 5 262 1310 5 1310 0
All my information for a particular Item number aggregates. This is good and how I need it to work with the exception of the Original Quanitty column. The problem I'm having is when I have two credit notes for one particular item. My Original Quantity is aggregating because both rows of raw data contain the original quantity for the order. When each row is processed in Webi, it sums up this field (Orig Qty).
Here is a sample of what two different rows with credit notes look like:
*Order Number Item Number Orig Qty Unit Price Cred Ord Cred Item Qty Rtrn Cred Price Ln#Cred Ln#Cred Uniq
ORD00219 R408A24 40.0000 220.000 00219 R408A24 10.000 220.0000 32 32 6978
ORD00219 R408A24 40.0000 220.000 00219 R408A24 30.0000 220.0000 32 32 7618
I was trying to compare these two lines of data which are the same order number and item number. These two lines both have credit notes (returns) on them. I was trying to compare information for the quantity by seeing if the credit line #'s match and the Credit Uniq ID is different, then I only want to use the original quantity once from one of the lines instead of putting in both orig. quantities and aggregating them. Most other credit notes are only one credit note per item, so I don't have to worry about this and can use the Original Quantity.
I went ahead and did a formula similar to this one below, but I still can't figure it out. I just keep getting # signs.
=If ( (Credlinenum) = (Credlinenum)) And ( (Crduniq) <> (Crduniq)) Then ( (Original Quantity) - (Quantity Returned)) Else ( (Original Quantity))
I tried using PREVIOUS, but it still didn't work. When I did that, it messed up the other items that had single credit notes on them.
Can you please point me in the right direction?
Edited by: cestevanes on Sep 25, 2009 10:24 AM
Edited by: cestevanes on Sep 25, 2009 10:27 AM
Edited by: cestevanes on Sep 25, 2009 10:33 AM
Edited by: cestevanes on Sep 25, 2009 10:40 AM