1 Reply Latest reply: Oct 3, 2018 2:09 PM by vamsee duggirala RSS

    Question: Roll up/Aggregate sales

    Hin Lok Lam

      Hi, I need some help on trying to aggregate sales through product hierarchy.


      I have four types of product, and these products group into 2 different types of product groups. (see below)


      If i want to find out how much Class 1 sales in month of Jan, does anyone know what the formula would be?



      ProductMonthSales ($)
      Product AJan500
      Product BJan200
      Product CFeb400
      Product DJan800
      Product AFeb550


      ProductProduct Group
      Product AClass 1
      Product BClass 1
      Product CClass 2
      Product DClass 2
        • Re: Question: Roll up/Aggregate sales
          vamsee duggirala

          Try the following


          Step1 --> Create a Map from Group Table with Product as Key and Group as Value

          Step 2 --> Applymap Group onto Sales Table

          Step 3 --> Perform Aggregation on Sales, in Sales table at Group and Month Level.




          Mapping Load Product, [Product Group];

          LOAD * INLINE [

              Product, Product Group

              Product A, Class 1

              Product B, Class 1

              Product C, Class 2

              Product D, Class 2



          LOAD *,

          ApplyMap('Group_Map', Product,NULL()) as Group;

          LOAD * INLINE [

              Product, Month, Sales ($)

              Product A, Jan, 500

              Product B, Jan, 200

              Product C, Feb, 400

              Product D, Jan, 800

              Product A, Feb, 550








          Sum([Sales ($)]) as Sales

          Resident Source

          Group By



          DROP Table Source;