topic Re: Positive and Negative Quantities in QlikView

Positive and Negative Quantities

jenmclean — Wed, 11 Dec 2013 14:56:53 GMT

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;

Re: Positive and Negative Quantities

wallinpeter — Wed, 11 Dec 2013 15:18:55 GMT

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)

Re: Positive and Negative Quantities

jenmclean — Wed, 11 Dec 2013 16:23:33 GMT

Peter,

Thank you, I knew there was a better way.

Jennie

Re: Positive and Negative Quantities

jenmclean — Mon, 16 Dec 2013 17:03:29 GMT

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

Re: Positive and Negative Quantities

wallinpeter — Mon, 16 Dec 2013 17:22:10 GMT

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

Re: Positive and Negative Quantities

jenmclean — Wed, 18 Dec 2013 19:19:53 GMT

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?

Re: Positive and Negative Quantities

wallinpeter — Wed, 18 Dec 2013 19:57:42 GMT

Try...

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