Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

hencovanee
Contributor II

sum(fieldname) after distinct of another field

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_idordernumbercontractvalue
12341502100
12341512100
12341522100

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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: sum(fieldname) after distinct of another field

Hi,

You can use the finction AGGR()

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

See this example:

3 Replies
Not applicable

Re: sum(fieldname) after distinct of another field

Hi,

You can use the finction AGGR()

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

See this example:

hencovanee
Contributor II

Re: sum(fieldname) after distinct of another field

Thanks for your quick respons ecorrea!

This works exactly the way I need!

Henco

Not applicable

Re: sum(fieldname) after distinct of another field

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 ^

Community Browser