Categorizing by percent of total in script

[Anonymous]

Hello,

could someone please tell me what is wrong in this code? I am trying to categorize suppliers to A,B or C depending on the percentage of total sales as in: these suppliers make 80% of the spend and are therefore 'A', these suppliers make then to the 95% and are 'B' and the rest are 'C'... everything works until the IF statements where i get an error saying "if takes 2-3 parameters...

ABC_1:

LOAD

     Supplier_id,

     //[Invoiced amount incl. VAT],

     [Invoiced amount incl. VAT] as ABC_spend

     resident TEMPAP

order by [Invoiced amount incl. VAT] desc, Supplier_id;

ABC_2:

NoConcatenate

load *

,if(rowno()=1,ABC_spend, Peek(CumulSpend,-1)+ABC_spend) AS CumulSpend

Resident ABC_1;

total = Peek('CumulSpend',-1,'ABC_2');

DROP TABLE ABC_1;

ABC_CAT:

NoConcatenate

LOAD * ,

CumulSpend/$(total),

if(CumulSpend/$(total) <=.8, 'A',

if(CumulSpend/$(total) <=.95, 'B',

'C')) AS CLASS

Resident ABC_2;

DROP Table ABC_2;

[Kushal_Chawda]

try this

if((CumulSpend/$(total))<=.8, 'A',

if((CumulSpend/$(total))<=.95, 'B',

'C')) AS CLASS

[sunny_talwar]

Another alternative if you don't want to use variables. Not sure which method is better, but just to add another possibility:

ABC_1:

LOAD Supplier_id,

    [Invoiced amount incl. VAT] as ABC_spend

Resident TEMPAP;

Left Join (ABC_1)

LOAD Sum([Invoiced amount incl. VAT]) as Total

Resident ABC_1;

ABC_CAT:

LOAD *,

     CumulSpend/Total as CumulPercent,

     If(CumulSpend/Total <= 0.80, 'A',

     If(CumulSpend/Total <= 0.95, 'B', 'C')) as CLASS;

LAOD *,

     If(RowNo() = 1, ABC_spend, RangeSum(Peek(CumulSpend), ABC_spend)) ax CumulSpend

Resident ABC_1

Order By [Invoiced amount incl. VAT] desc;

DROP Table ABC_1;