topic sum distinct aggregation in QlikView

sum distinct aggregation

Tue, 05 Jun 2012 14:40:51 GMT

hi all

i have a case here

as shown on the picture, i have a pivot table that show the totalDebt of companies's shareHolders of a group,

i use (sum([totalDebt])) to sum the debt of each shareHolder's debt, so the result is 51200 (with debt of holder2 & holder4 are totalled 2 times), no matter the holders are in a company or many (holder2 and holder4), but the desired result is 32100 (distinct per shareholder),

i've been trying to use this function as (Sum(Aggr(DISTINCT [PH],shareholder))), but the result seems wrong.

could anyone help me ?

thanks

sum distinct aggregation

swuehl — Tue, 05 Jun 2012 15:06:36 GMT

Hi,

your problem seems very similar to that one I answered just recently:

http://community.qlik.com/message/225437

The idea is to use dimensionality() to check for calculation in total or details line and then use aggr() function with DISTINCT resp. NODISTINCT qualifier.

Hope this helps,

Stefan

Re: sum distinct aggregation

Thu, 07 Jun 2012 06:52:35 GMT

hi stefan,

thank you for your reference,

but still i can't figure out to use the dimensionality() function in this case,

i already attached the qlikview file and data sample ,

would u like to check the file?

thanks

sum distinct aggregation

swuehl — Thu, 07 Jun 2012 11:19:23 GMT

Not 100% sure if this is what you need, are you looking for

=Sum(Aggr(DISTINCT ([OSPH Pemegang Saham]),Debitur, norek,nopin ))

Re: sum distinct aggregation

Fri, 08 Jun 2012 08:27:12 GMT

thanks stefan,

yeah that's what i'm looking for