sum(fieldname) after distinct of another field

[Anonymous]

Hi,

We are working with contracts. So, a lot of our customers have a contract with a value in euro's. For example, 2.100 euro / 4.200 euro.

Now, I would like to sum all these contractvalues. The problem is that I get more rows per contract after loading the script. This is correct, because all the ordernumbers have a connection with a contract, so the value of the contract comes as much as the orders which are linked.

What I would like to do is first make a distinct on contract_id and then sum the values of the contracts. It has to be an expression, so I can show it in a table per daterange or salesman.

Is there someone who can help me?

Example:

contract_id	ordernumber	contractvalue
1234	150	2100
1234	151	2100
1234	152	2100

A sum of the contractvalues in this case is 3 x 2100 = 6300, but it is the same contract.

So first of all I would like to distinct the contract_id and then sum the values.

Thanks in advance.

Kind regards,

Henco

[#2 · Accepted solution]

Hi,

You can use the finction AGGR()

SUM(AGGR(SUM(DISTINCT contractvalue), contract_id))

See this example:

[Anonymous]

Thanks for your quick respons ecorrea!

This works exactly the way I need!

Henco

[#4]

Any idea why this function will not work in a text box expression.  For example, I would like the text box to return value "2100."

SUM(AGGR(SUM(DISTINCT contractvalue), contract_id))

This function only seems to work in tables but not text boxes ^