ABC analysis in Script Editor and adding new dimension-Location

[haribabugv]

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

stalwar1‌

[sunny_talwar · Accepted solution]

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;

[haribabugv]

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

stalwar1‌

[sunny_talwar]

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;