Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
Product | Month | Sales ($) |
---|---|---|
Product A | Jan | 500 |
Product B | Jan | 200 |
Product C | Feb | 400 |
Product D | Jan | 800 |
Product A | Feb | 550 |
Product | Product Group |
---|---|
Product A | Class 1 |
Product B | Class 1 |
Product C | Class 2 |
Product D | Class 2 |
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;
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;