evaluate fieldvalues to group

[dickelsa]

hi,

I would like to evaluate fieldvalues, and create a field that narrows down the group of persons.

for instance,

if a person makes less than 50% of average Margin, i would like to call the group 'below average'

next, group 'average' makes between 50% and 120% of average Margin.

Group  'above average' should contain only the persons that are above 120%  of average Margin.

so, what i want is 2 fields: 1 field with the groups 'Average','Below Average','Above average'/

1 field will contain all the persons.

Margin is calculated to substract Costs from Profit. the datafield for Costs and Profit is called 'Data'.

(to get an idea of my thoughts:

i tried doing this in the script:

Join(Kostenplaats)

LOAD

Nummerbord,

if(

100>$((-sum({<KostenOmzet={'Omzet'}>}Data)-sum({<KostenOmzet={'Kosten'}>}Data)))

/$((-sum({1<KostenOmzet={'Omzet'}>}Data)-sum({1<KostenOmzet={'Kosten'}>}Data))/count({1}DISTINCT Nummerbord))>1

,'Kopgroep', if(

1>$((-sum({<KostenOmzet={'Omzet'}>}Data)-sum({<KostenOmzet={'Kosten'}>}Data)))

/$((-sum({1<KostenOmzet={'Omzet'}>}Data)-sum({1<KostenOmzet={'Kosten'}>}Data))/count({1}DISTINCT Nummerbord))>0.5

,'Gemiddeld',if(

0.5>$((-sum({<KostenOmzet={'Omzet'}>}Data)-sum({<KostenOmzet={'Kosten'}>}Data)))

/$((-sum({1<KostenOmzet={'Omzet'}>}Data)-sum({1<KostenOmzet={'Kosten'}>}Data))/count({1}DISTINCT Nummerbord)),'Onder Gemiddeld',''

)))as Groepensoort

Note that: Kosten=Costs, Omzet=Profit, Nummerbord=Licenseplate, kopgroep= Leading group, gemiddeld= average, onder gemiddeld= below average. KostenOmzet= CostsProfit. Groepensoort=Grouptype.

Can you please help me out? i dont know what to do.

(see attached for testdata)

Best regards

Dick