Sum Distinct

[stvn_003]

I want to show a total on a label, but when I add the amount ignored repeated amounts, which relamente I want is to add the amounts of each sales order once, but instead only sum the amount of each sales order which they are different.

This is the formula I use:

SUM( {$<occ_cesvl = {'01'}, occ_sclnok = {'N'}>} DISTINCT occ_imp_bru)

As exist two equal amounts, only considers one.

[sunny_talwar · Accepted solution]

Try this instead:

=Sum(Aggr(Sum({$<occ_cesvl = {'01'}, occ_sclnok = {'N'}>} occ_imp_bru), SalesOrderField))

=Sum(Aggr(Avg({$<occ_cesvl = {'01'}, occ_sclnok = {'N'}>} occ_imp_bru), SalesOrderField))

[sunny_talwar]

In your case if SalesOrderField is called [Nro OC], then this:

=Sum(Aggr(Sum({$<occ_cesvl = {'01'}, occ_sclnok = {'N'}>} occ_imp_bru), [Nro OC]))

=Sum(Aggr(Avg({$<occ_cesvl = {'01'}, occ_sclnok = {'N'}>} occ_imp_bru), [Nro OC]))

[antoniotiman]

Try to remove DISTINCT

[jagan]

HI,

Try this

=Sum({<occ_cesvl = {'01'}, occ_sclnok = {'N'}>} Aggr(Sum({<occ_cesvl = {'01'}, occ_sclnok = {'N'}>} DISTINCT occ_imp_bru), [Nro OC]))

Regards,

Jagan.

[stvn_003]

Thanks Sunny T, great!