1 Reply Latest reply: Jun 5, 2010 1:59 PM by Gordon Savage RSS

    Help with SUM( ) function required - possibly need AGGR?


      Hope you can help.

      I have a table containing a set of targets, by Sector for a set of measures, each one relating to a particular month

      In a seperate table I have a set of sales figures, which are measured against the targets.

      Each month, multiple sales for each sector are measured each target.

      My target table contains the following fields

      Sector TargetMonth TargetValue

      UK Apr-10 10

      India Apr-10 15

      UK May-10 20

      ... ...

      In a straight table I am trying to add up the targets for each sector for the year to date, using the following formula

      SUM (IF(TargetMonth >= 'Apr 10' AND TargetMonth <= 'May-10', TargetValue))

      However, the fugure i get back is far too high, i think this is because it is adding a TargetValue for each sale linked to a particular target.

      How can i make it only include a particular target once across the date range?

      Strangely when i do it for a single month it's fine! e.g. SUM (IF(TargetMonth = 'Apr 10' , TargetValue))

      Any help of advice is greatly appreciated.