3 Replies Latest reply: Jun 6, 2012 9:37 AM by Yaniv Egozi RSS

    Sum total in load - problem

    Yaniv Egozi
      G'day,
      I am fairly new to QV
      I have sales table
      Invoice #ProdProduct_Line  (PL)Sales
      1A10100
      1B11150
      1C17-50
      1Total200
      2D10100
      2E12200
      2Total300
      3D10100
      3F11150
      3G11200
      3Z17-50
      3Total400
      I want to add 2 fields that calculate the total  of product line and the total inv without the product line whice will look like this.
      Invoice #ProdProduct_Line  (PL)SalesTotal  For PL 17 in InvTotal  Inv Without PL = 17
      1A10100-50250
      1B11150-50250
      1C17-50-50250
      1Total200
      2D101000300
      2E122000300
      2Total300
      3D10100-150450
      3F11150-150450
      3G11200-150450
      3W17-100-150450
      3Z17-50-150450
      3Total300
      I have tried to use sum(total<Invoice_no>(if(product_line <> 17,sales)))
      but found that I cannot use sum total in the load script.
      any help will be appreciated
      Yaniv
        • Sum total in load - problem
          Jonathan Dienst

          Yaniv

           

          In the load script, you would use group by to get the same effect,

           

           

          LOAD Sum(If (product_line <> 17, Sales)) As Sales

               ...

          From Sales.xlsx

          Group By Invoice_no

           

          As a general suggestion, are you sure that you want to filter the values in the load script? Usually I would not filter the incoming data like this and rather filter the expressions in the chart or table.

           

          Hope that helps

          Jonathan

          • Re: Sum total in load - problem
            Toni Kautto

            I agree with Jonathan that it seems better to do your calculation in the chart object, to make sure that the result corresponds to the current selections you have made in the application. For example if you would has exclided the W product the static sum from load script could be misleading in a chart object. See attached example, where you also are able to alter the prodline to sum over dynamically.

            • Sum total in load - problem
              Yaniv Egozi

              Jonatan Thanks

              I know that it's best to to filter it in the chart which is what I did at the begining, but my problem was that had to leave the product_line as a dimention in the chart as when I took it off the result changed.

              Loading it in the script gave me the option to calculate the new_sales  per each produc

               

              Toni I have tried your suggestion and my problem with it is if I select PROD A for example and PLPickList 17 it doesn't give me the -50.