6 Replies Latest reply: Dec 18, 2013 2:57 PM by Peter Wallin RSS

    Positive and Negative Quantities

    Jennie Elliott

      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
          Peter Wallin

          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
            Jennie Elliott

            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