Web Intelligence comparing rows

[Former Member]

Hi,

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?

Thanks,

Charlette

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

[Former Member]

I think your universe probably needs to have the Original Quantity twice - one as a measure (which is what you have) so that you can add it up automatically, and one as a dimension to be used in cases like the one you're looking at. The dimension won't automatically aggregate so you'll just get the raw data.

-Dell

[Former Member]

Thank you, you got me headed in the right direction. I ended up just creating a new dimension for the quantity, like you suggested, but I did it within the report instead. I also made sure my rows weren't aggregating and that seemed to work. Thank you for all your help!

-Charlette