topic Re: Sum Distinct in QlikView

Sum Distinct

stvn_003 — Fri, 31 Jul 2015 16:15:28 GMT

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.

Re: Sum Distinct

sunny_talwar — Fri, 31 Jul 2015 16:18:06 GMT

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

Re: Sum Distinct

sunny_talwar — Fri, 31 Jul 2015 16:20:02 GMT

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

Re: Sum Distinct

antoniotiman — Fri, 31 Jul 2015 16:21:26 GMT

Try to remove DISTINCT

Re: Sum Distinct

jagan — Fri, 31 Jul 2015 16:34:35 GMT

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.

Re: Sum Distinct

stvn_003 — Fri, 31 Jul 2015 16:39:57 GMT

Thanks Sunny T, great!