Usage of Aggr() and Dimensionality() in custom total row pivot table

[bartvanwetten]

Dear,

in the pivot table in the attached qlikview document I want the REAL TOTAL to be the sum of rows in the Debet column.

I know I must use AGGR() somewhere but can't figure the right expression out.

Also I found out I can make my own custom Total so I'm trying to use Dimensionality().

Can someone help me out with the right expression ?

Thx in advance!

[jerem1234 · Accepted solution]

For Debet, you'll want to use the formula:

if(Dimensionality()=0,

sum(aggr(if(sum({<acct_type={1}>}amount)>0, sum({<acct_type={1}>}amount)), acct_nr, Grootboek_Grootboekomschrijving, AdministratieCode)),

if(sum(amount)>0,sum({<acct_type={1}>}amount))

)

For Credit, you'll want:

if(Dimensionality()=0,

sum(aggr(if(sum({<acct_type={1}>}amount)<0, sum({<acct_type={1}>}amount*-1)),acct_nr, Grootboek_Grootboekomschrijving, AdministratieCode)),

if(sum(amount)<0,-sum({<acct_type={1}>}amount))

)

Hope this helps!

[bartvanwetten]

Dear Jeremiah,

it's EXACTLY what I needed and I now understand what needs to be doen!

Many, many thanks!

Bart