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
