sum distinct aggregation

[#1]

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

[swuehl]

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

[#3]

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

[swuehl · Accepted solution]

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

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

?

[#5]

thanks stefan,

yeah that's what i'm looking for