Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello QV Team,
I have completed ABC analysis at SKU level in script editor as attached.
I need to do ABC analysis at SKU-Location level again in the editor.
I want the whole script to be rewritten in such a way that I get ABC analysis output for SKU-Location level instead of SKU level.
Can one throw somelight here?
I have shared the expected output in in the attached excel sheet.
CURRENT OUTPUT
EXPECTED OUTPUT
Looking for solutions in the script editor not in the front end charts
Something like this
Table1:
LOAD SKU,
Location,
Sales
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
Table2:
NoConcatenate
LOAD SKU,
Location,
Sum(Sales) as SumSalesSKULocation
Resident Table1
Group By Location, SKU;
Left Join (Table2)
LOAD Location,
Sum(SumSalesSKULocation) as SumSalesLocation
Resident Table2
Group By Location;
FinalTable:
LOAD *,
If(Location = Previous(Location), RangeSum(Peek('CumSum'), SumSalesSKULocation), SumSalesSKULocation) as CumSum,
If(Location = Previous(Location), RangeSum(Peek('CumSum'), SumSalesSKULocation), SumSalesSKULocation)/SumSalesLocation as Pareto
Resident Table2
Order By Location, SumSalesSKULocation desc;
DROP Table Table1, Table2;
Something like this
Table1:
LOAD SKU,
Location,
Sales
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
Table2:
NoConcatenate
LOAD SKU,
Location,
Sum(Sales) as SumSalesSKULocation
Resident Table1
Group By Location, SKU;
Left Join (Table2)
LOAD Location,
Sum(SumSalesSKULocation) as SumSalesLocation
Resident Table2
Group By Location;
FinalTable:
LOAD *,
If(Location = Previous(Location), RangeSum(Peek('CumSum'), SumSalesSKULocation), SumSalesSKULocation) as CumSum,
If(Location = Previous(Location), RangeSum(Peek('CumSum'), SumSalesSKULocation), SumSalesSKULocation)/SumSalesLocation as Pareto
Resident Table2
Order By Location, SumSalesSKULocation desc;
DROP Table Table1, Table2;
Thanks Sunny,
In case if i need to material pareto% what should be done as shown below (column G)?
I tried using the below line to get the material count but not successful.
IF(Location = Previous(Location), RangeSum(Peek(ID),1),1) as ID
This
Table1:
LOAD SKU,
Location,
Sales
FROM
Book1.xlsx
(ooxml, embedded labels, table is Sheet1);
Table2:
NoConcatenate
LOAD SKU,
Location,
Sum(Sales) as SumSalesSKULocation
Resident Table1
Group By Location, SKU;
Left Join (Table2)
LOAD Location,
Sum(SumSalesSKULocation) as SumSalesLocation,
Count(SKU) as Count
Resident Table2
Group By Location;
FinalTable:
LOAD *,
If(Location = Previous(Location), RangeSum(Peek('CumSum'), SumSalesSKULocation), SumSalesSKULocation) as CumSum,
If(Location = Previous(Location), RangeSum(Peek('CumSum'), SumSalesSKULocation), SumSalesSKULocation)/SumSalesLocation as Pareto,
If(Location = Previous(Location), RangeSum(Peek('ID'), 1), 1) as ID,
If(Location = Previous(Location), RangeSum(Peek('ID'), 1), 1)/Count as MaterialPareto
Resident Table2
Order By Location, SumSalesSKULocation desc;
DROP Table Table1, Table2;