5 Replies Latest reply: Jan 12, 2013 2:18 PM by Nagaian Krishnamoorthy RSS

    Sum with where statement

    Jan Gerbecks

      Hello Qlikview experts,

       

      I am a newbee on Qlikview.

      I use Qlikview 7.5.

       

      I wan to create a sales report.

      Need a formula to calculate new field “Sum” wich is the sum of field Quantity where the fields “Cust”, “Item Numb”, “Customer Item and “”Month” are same.

       

      Cust

      Item Numb

      Customer Item

      Month

      Quantity

      Date

      Sum

      100376

      30308A00Z

      1254489

      1

      36

      8-01-13

      36

      100003

      30309B00Z

      1688560

      1

      24

      2-01-13

      24

      100003

      30309B00Z

      1688560

      2

      24

      8-02-13

      24

      100003

      30327A00Z

      1366897

      1

      12

      3-01-13

      24

      100003

      30327A00Z

      1366897

      1

      12

      7-01-13

      24

      100003

      30327A00Z

      1366897

      2

      12

      8-02-13

      12

      100003

      30329A00Z

      1369921

      1

      30

      7-01-13

      30

      100003

      30345A00Z

      1425520

      1

      32

      4-01-13

      32

      100003

      30369A00Z

      1450325

      1

      16

      3-01-13

      16

      100003

      30369A00Z

      1450325

      2

      16

      7-02-13

      16

      100447

      30309B00Z

      A9602641901

      1

      2

      2-01-13

      2

      100447

      30309B00Z

      A9602641901

      2

      30

      8-02-13

      30

      100447

      30327A00Z

      A9602641901

      1

      16

      3-01-13

      16

      100447

      30327A00Z

      A9602641901

      1

      16

      7-01-13

      32

      100447

      30327A00Z

      A9602642001

      2

      2

      8-02-13

      2

      100447

      30329A00Z

      A9602642001

      1

      30

      7-01-13

      30

      Any help will be apreciated,

      Regards,

      Jan G.

        • Re: Sum with where statement
          Nagaian Krishnamoorthy

          If you want to calculate the sum in script, the following may help you:

           

          If the detailed data table name is Details

           

          Summary:

          NoConcatenate LOAD Cust, [Item Numb], [Customer Item], Month, Sum(Quantity) as SumQty

               resident Details Group By Cust, [Item Numb], [Customer Item], Month;

           

          If you want to display the sum on a chart, create a pivot table chart with Cust, [Item Numb], [Customer Item], Month as dimensions and Sum(Quantity) as expresion.

           

          Hope this helps.