Positive and Negative Quantities

[jenmclean]

I am pulling invoices and credits from my SQL table called Details; 1 is an invoice and 17 is a credit and storing into a QVD. Then I want to perform certain calculations before gathering it all into a Transactions QVD later in the script

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.

Below is the original script written by someone else. Is this correct? Is there a better way to write this?

//Transaction Details - Pulls Invoices and Credits Only
SQL SELECT *
FROM "TirePower".dbo.Details
WHERE [DET_TransTypeID]=1 or
[DET_TransTypeID]=17;
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_TransTypeID=1, DET_Qty, DET_Qty*(-1))) as DET_NetQty,   //This makes Refunds and Credits Negative Qty's.
((DET_FET * (If(DET_TransTypeID=1, DET_Qty, DET_Qty* (-1)))) + DET_Amount) -
((DET_FET * (If(DET_TransTypeID=1, DET_Qty, DET_Qty* (-1)))) + (DET_UnitCostLast*(If(DET_TransTypeID=1, DET_Qty, DET_Qty* (-1))))) as GrossRev
Resident Detail
Where Match(DET_TransTypeID, 1, 17)>0;
Store * FROM Detail_Calc Into C:\QlikView\QlikView Development\QVDocuments\SourceDocuments\QVD\TS\Detail_Calc.qvd (qvd);

Drop Table Detail;
Drop Table Detail_Calc;

[wallinpeter · Accepted solution]

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)

[jenmclean]

Peter,

Thank you, I knew there was a better way.

Jennie

[jenmclean]

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 *

FROM "TirePower-SchierlTire_WI".dbo.Details

WHERE [DET_TransTypeID]=1 or

   [DET_TransTypeID]=17;

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

PART 2

//******Calculations******

     //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

    //Margin

    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

[wallinpeter]

Not sure I understand equations. If Ext.Price = UnitPrice - Ext.Cost, then...

shouldn't Ext.Price be....

(((DET_Amount/DET_NetQty) + DET_FET)) - ((DET_FET + DET_UnitCostLast) * DET_NetQty)

or am I missing something

[jenmclean]

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?

[wallinpeter]

Try...

if (DET_Amount<=0 and DET_Qty>0, DET_Qty*(-1), DET_Qty)