This code is not doing what you stated....
According to your statement you want to implement the exact same logic regardless of TranType...
For Transaction 17 (Credits), if there is a positive quanity and a negative price, I need to turn that positive quantity to a negative quantity.
For Transaction 1 (Invoices) if there is a positive quanity and a negative price, I need to turn that positive quantity to a negative quantity.
Anywhere you would use DET_Qty, replace with this....
if (DET_Amount<0 and DET_Qty>0, DET_Qty*(-1), DET_Qty)
I am having one further issue with this problem. My two parts of the code are below plus a screen shot of the exported data. Part 1 shows the updated formula that Peter suggested which has been validated.
PART 2 shows the formula's for calculating specific items. The screenshot shows an example of a calculation not working properly which then puts my numbers off for that day. We narrowed it down to a specific invoice which is showing in the screen shot. See notes on screen shot...
What is the best way to handle this?
PART 1 (from Peter's response)
Detail: //Transaction Details - Pulls Invoices and Credits Only
SQL SELECT *
WHERE [DET_TransTypeID]=1 or
Store * FROM Detail Into C:\QlikView\QlikView Development\QVDocuments\SourceDocuments\QVD\TS\Detail.qvd (qvd);
Detail_Calc: //Calculates Negative QTY and Gross Revenue - Use this one for building document
NoConcatenate Load * ,
if(DET_Amount < 0 and DET_Qty > 0, DET_Qty*(-1), DET_Qty) as DET_NetQty //New Forumula for Negative Quantity 12/11/13; validated
//Gross Revenue and Targets
numSum(DET_NetQty) as TARGET|BaseUnitsSold,
numSum(DET_NetQty) * 1.15 as TARGET|StretchUnitsSold,
//Cost and Price Calculations
(DET_FET + DET_UnitCostLast) as TP|UnitCost,
((DET_Amount/DET_NetQty) + DET_FET) as TP|UnitPrice,
(DET_FET + DET_UnitCostLast) * DET_NetQty as TP|Ext.Cost,
(DET_FET * DET_NetQty) + DET_Amount as TP|Ext.Price,
(((DET_FET * DET_NetQty) + DET_Amount)- ((DET_FET + DET_UnitCostLast) * DET_NetQty)) as GR|Final, //Ext.Price-Ext.Cost * NetQty
(((DET_FET * DET_NetQty) + DET_Amount)- ((DET_FET + DET_UnitCostLast) * DET_NetQty)) * 1.15 as TARGET|StretchRevenue, //Ext.Price-Ext.Cost * NetQty
Num((((DET_FET) * DET_NetQty + DET_Amount) - ((DET_FET + DET_UnitCostLast) * DET_NetQty)) / ((DET_FET) * DET_NetQty + DET_Amount),'#,##0.00%') as TP|Margin //Ext.Price - Ext.Cost / Ext.Price
I beleive the issue is due to the Invoice Amount (DET_Amount) being $0.00. This example is for an oil change which has the line item for the oil used in the service (for invnentory) but since it is part of a package the $ amount is not included on that line.
Basically, TP|Ext.Cost needs to be negative if there is a $0.00 in DET_Amount which should then make GR|Final a positive.
The rest of the line items on this credit are correct.
Does that make more sense?