Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Gurus,
I have made a ABC Analysis of my supplier in a combo chart and i am looking to put a filter where it shows me the A,B,C category.
I want to do the calculation in script so that i can call it in front end.
Criteria for categorisation is . 80% of total purchase are categorised as A , 80% - 90% as B , else C.
I am not sure how can i write it in script so that it gives me the filter in sheet.
Here s the table
SUPPLIER:
LOAD
%MANDT_LIFNR,
IF(SUM(NetValue*ExchangeRate)/(SUM( Total NetValue*ExchangeRate))> '0.8 ','Gold', IF(SUM(NetValue*ExchangeRate)/(SUM(NetValue*ExchangeRate) < '0.8 ' and IF(SUM(NetValue*ExchangeRate)/(SUM(NetValue*ExchangeRate)) > '0.9' ,'Silver', 'Regular')) as Vendor_Category
group by %MANDT_LIFNR;
DROP FieldS %MANDT_LIFNR,ExchangeRate From EKPO;
I am trying this but no help.
IF(SUM(NetValue*ExchangeRate)/(SUM( Total NetValue*ExchangeRate)) > '0.8 ','A', IF(SUM(NetValue*ExchangeRate)/(SUM(NetValue*ExchangeRate) < '0.8 ' and IF(SUM(NetValue*ExchangeRate)/(SUM(NetValue*ExchangeRate)) > '0.9' ,'Silver', 'Regular')) as Vendor_Category
First evaluate the TOTAL SUM( NetValue*ExchangeRate) in a separate table
Next SUM( NetValue*ExchangeRate) at %MANDT_LIFNR
Last Left JOin and Compare % range
temp:
Load SUM(NetValue*ExchangeRate) as TotalNetValue Resident Supplier;
Left Join(temp)
Load %MANDT_LIFNR, SUM(NetValue*ExchangeRate) as ValuePerKey
Resident Supplier Group by %MANDT_LIFNR;
NOCONCATENATE
Final:
LOAD
%MANDT_LIFNR,
IF((ValuePerKey/TotalNetValue) > 0.9 ,'Gold',
IF((ValuePerKey/TotalNetValue) > 0.8 ,'Silver', 'Regular')) as Vendor_Category
Resident temp;
drop table temp;
>90% Gold
>80<90% Silver
<80% Regular