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.