4 Replies Latest reply: Dec 1, 2010 12:15 PM by a1qczzz RSS

    Exclude negative values in Total aggregate value

    a1qczzz

      I have a Pivot Table where I am displaying how the Commodities perform over a date range, see Table below.

       

      My problem is calculating the Total Leakage value, which should exclude all negative values in the row.

       

      The calculations

       

      1. Leakage calculation in its simplest form = (Lower GM%) - (Calculated GM%) * Sales

       

      2. Where Lower GM% is selected by users from a table and has a value 0 for this example

       

      3. Calculated GM% = (Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT)

      and is shown in the table below

       

      4. I display only the positive leakage values in the table and force the negative values to 0. I use this formula in QV to achieve this:

      =Rangemax(0, ((Lower - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100 )

       

      My Problem

      5. My problem is when I am calculating the TOTAL LEAKAGE. I cannot exclude those negative values from my total leakage value. I have tried it using the following formula:

      =Num((Aggr

      (if(

      (((Lower - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100) >= 0,

      ((Lower - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100,

      0),

      ESIS_Commodity))/1000,

      '#####0','.',',')

       

       

      Total Leakage '000

      Commodity

      Date

      2010-10

      2010-09

       

      5790 NEOPLAST TAPES AND DRESSINGS

      Sales

      20,429

      1,037

       

      5790 NEOPLAST TAPES AND DRESSINGS

      GM%

      31.21%

      -91,018.44%

      937

      5790 NEOPLAST TAPES AND DRESSINGS

      Leakage

      0

      943679.16



       

       

       

      For example:

      In 2010/10, Leakage ~ -6375.89 (it's negative, so I display 0)

      In 2010/9, Leakage ~ 943679

      So Total Leakage should only count +ve values and display ~ 943,000

      Instead it displays as ~937,000 = (943679-6375.89)

       

      Would you have any suggestions on how I can exclude the negatives and what I am doing wrong? The business to getting impatient to release this tool now and I do not even know if it possible to even do this in QV9?

       

      Many thanks in advance

      Sahar

        • Exclude negative values in Total aggregate value

          Hello Sahar,

          one idea is to use an addional field as flag. This field contains a '1' for positve values and a '0' for the negativ values. You can easily achieve this in your load script with an if-Statement like

          load .... if (Amount< 0, 0,1) As AmountFlag.
          After reloading you are able to use the flag in sum() like this
          =Sum(AMOUNT * AmountFlag)
          and you will sum up automatically only the positive values of AMOUNT.

          HtH

          Roland

            • Exclude negative values in Total aggregate value
              a1qczzz

              Hi Roland

              Thanks for the quick reply. It did cross my mind to load the calculation in my SQL. However, 'Amount' is a dynamic value. It changes depending on what value, users have picked as their Lower value.

              The formula behind it is:

              Amount = (Lower {This value is picked by users from a table and have values 0 to 90} - CalculatedGM%) * SalesAmount

              How will I manipulate the Amount value in my Load script?

                • Exclude negative values in Total aggregate value

                  Sorry Sahar,

                  I did'nt read your post carefully. In a load script you can't guess any users choice. So Set Analysis has to do the work. Can you send a little sample application that makes it much easier to examine?

                  Regards, Roland

                    • Exclude negative values in Total aggregate value
                      a1qczzz

                      HI Roland

                      Managed to get it to work finally with a few nested loops around the original formula:

                      Many thanks for your help thus far, Sahar

                       

                      =Aggr( SUM(Num((Aggr

                      ( if(

                      (((AVG(Lower) - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100) >= 0,

                      ((AVG(Lower) - ((Sum(SALES_AMOUNT) - Sum(COST_AMOUNT))/sum(SALES_AMOUNT))*100) * Sum(SALES_AMOUNT))/100,

                      0),

                      ESIS_Commodity,Date)),

                      '#####0','.',',')),ESIS_Commodity)