Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
New Contributor III

Sum Distinct

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.

Sin título.jpg

1 Solution

Accepted Solutions
Highlighted

Re: Sum Distinct

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))

View solution in original post

5 Replies
Highlighted

Re: Sum Distinct

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))

View solution in original post

Highlighted

Re: Sum Distinct

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]))

Highlighted
Honored Contributor III

Re: Sum Distinct

Try to remove DISTINCT

Highlighted
MVP & Luminary
MVP & Luminary

Re: Sum Distinct

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.

Highlighted
New Contributor III

Re: Sum Distinct

Thanks Sunny T, great!