topic ABC analysis in Script Editor and adding new dimension-Location in QlikView

ABC analysis in Script Editor and adding new dimension-Location

haribabugv — Wed, 25 Nov 2020 16:16:04 GMT

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‌

Re: ABC analysis in Script Editor and adding new dimension-Location

sunny_talwar — Mon, 11 Dec 2017 18:17:57 GMT

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;

Re: ABC analysis in Script Editor and adding new dimension-Location

haribabugv — Mon, 11 Dec 2017 19:27:56 GMT

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‌

Re: ABC analysis in Script Editor and adding new dimension-Location

sunny_talwar — Mon, 11 Dec 2017 19:31:51 GMT

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;