17 Replies Latest reply: Dec 15, 2014 1:44 AM by Nayan Lalla RSS

    Creating a Total Column

    Nayan Lalla


      Hi

       

      I have a sample of a straight table. (see below).  Sales per item per Branch.

      I want to create a total column per item ( ie i want to create the column highlighted in yellow).

       

      How do I do this.

       

      kind regards

      Nayan

       

      ITEM  CODEBranch NameSalesTotal Sales per Item
      ABCBloemfontein2832,145
      ABCBotswana312,145
      ABCCape Town2682,145
      ABCDurban1552,145
      ABCEast London812,145
      ABCJohannesburg2282,145
      ABCNamibia3202,145
      ABCNelspruit4002,145
      ABCPolokwane802,145
      ABCPort Elizabeth1892,145
      ABCSwaziland02,145
      ABCUpington1102,145
      ABCTotal2,1452,145
      DEFBloemfontein971,529
      DEFBotswana31,529
      DEFCape Town1381,529
      DEFDurban1581,529
      DEFEast London791,529
      DEFJohannesburg6141,529
      DEFNamibia1091,529
      DEFNelspruit771,529
      DEFPolokwane151,529
      DEFPort Elizabeth851,529
      DEFSwaziland1521,529
      DEFUpington21,529
      DEFTotal1,5291,529
      3,6741,529
        • Re: Creating a Total Column
          anbu cheliyan

          =Aggr(Sum(Sales),[ITEM CODE])

          • Re: Creating a Total Column

            or you can use

            =sum(TOTAL <[ITEM CODE]> Sales)

            • Re: Creating a Total Column
              Anand Chouhan

              Another way if you add the Sum fields in the load script

               

              Tab1:
              LOAD    [ITEM CODE], [Branch Name], Sales, [Total Sales per Item];
              LOAD * INLINE [
                  ITEM CODE, Branch Name, Sales, Total Sales per Item
                  ABC, Bloemfontein, 283, 2145
                  ABC, Botswana, 31, 2145
                  ABC, Cape Town, 268, 2145
                  ABC, Durban, 155, 2145
                  ABC, East London, 81, 2145
                  ABC, Johannesburg, 228, 2145
                  ABC, Namibia, 320, 2145
                  ABC, Nelspruit, 400, 2145
                  ABC, Polokwane, 80, 2145
                  ABC, Port Elizabeth, 189, 2145
                  ABC, Swaziland, 0, 2145
                  ABC, Upington, 110, 2145
                  DEF, Bloemfontein, 97, 1529
                  DEF, Botswana, 3, 1529
                  DEF, Cape Town, 138, 1529
                  DEF, Durban,    158, 1529
                  DEF, East London, 79, 1529
                  DEF, Johannesburg, 614, 1529
                  DEF, Namibia, 109, 1529
                  DEF, Nelspruit, 77, 1529
                  DEF, Polokwane, 15, 1529
                  DEF, Port Elizabeth, 85, 1529
                  DEF, Swaziland, 152, 1529
                  DEF, Upington, 2, 1529
              ];

               

              Left Join

               

              LOAD
              [ITEM CODE],
              sum( Sales) as [Total Sum] //For Total Sum
              Resident Tab1 Group By [ITEM CODE];

               

              And then in chart

              Dim1:-[ITEM CODE], [Branch Name]

              Expre:- Sum(Sales)

              Expre:- [Total Sum]

               

              Or simple in the front end

              Sum(Sales)

              Sum(TOTAL <[ITEM CODE]> Sales)

               

              Regards

              Anand