Categorization in Script Editor

Qlik1Enthusiast — Fri, 20 May 2022 13:00:48 GMT

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

Re: Categorization in Script Editor

vinieme12 — Sat, 21 May 2022 14:56:53 GMT

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

topic Re: Categorization in Script Editor in App Development

Categorization in Script Editor

Re: Categorization in Script Editor