0 Replies Latest reply: Jul 11, 2014 10:39 AM by dick elsman RSS

    evaluate fieldvalues to group

    dick elsman

      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