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?

       

      Thanks,

      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.

           

           

          Group_Map:

          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

          ];

          Source:

          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

          ];

           

          Aggregated_Table:

           

          LOAD

          Group,

          Month,

          Sum([Sales ($)]) as Sales

          Resident Source

          Group By

          Group,

          Month;

          DROP Table Source;