Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
jenmclean
New Contributor II

Positive and Negative Quantities

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;

1 Solution

Accepted Solutions
wallinpeter
New Contributor III

Re: Positive and Negative Quantities

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)

6 Replies
wallinpeter
New Contributor III

Re: Positive and Negative Quantities

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
New Contributor II

Re: Positive and Negative Quantities

Peter,

Thank you, I knew there was a better way.

Jennie

jenmclean
New Contributor II

Re: Positive and Negative Quantities

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

qv-calculation.png

wallinpeter
New Contributor III

Re: Positive and Negative Quantities

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
New Contributor II

Re: Positive and Negative Quantities


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
New Contributor III

Re: Positive and Negative Quantities

Try...

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

Community Browser