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

Fri, 04 Jun 2010 22:52:09 GMT

Hello,

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.

Sat, 05 Jun 2010 17:59:11 GMT

Why not calculate the sum in the script and then use it as a dimension (its more efficient)?

Load sector,

sum(TargetValue) as TotTargetValue

group by sector

from targetstable

where TargetMonth>= startmonth

Regards,

Gordon

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