Announcements
cancel
Showing results for
Did you mean:
Contributor II

## aggr() not working with 0

dear all,

im having trouble with a seemingly easy problem:

I want to calculate the sum of multiple aggr() values. I have prepared an example table:

VAT Number and Cust Number are the dimensions and Invoice, creditnotes and payments are the measures.

• aggr( nodistinct sum(invoice), 'VAT number')
• aggr( nodistinct sum(creditnotes),'VAT number')
• aggr( nodistinct sum(payments),'VAT number')
 VAT Number Cust Number invoice creditnotes payments headoffice invoices headoffice creditnotes headoffice payments total AAABBBB C1 1500 -25 -150 15000 -75 -200 14725 AAABBBB C2 8000 0 0 15000 -75 -200 14725 AAABBBB C3 1500 -50 -50 15000 -75 -200 14725 AAABBBB C4 4000 0 0 15000 -75 -200 14725 AAACCCC C5 350 0 -150 506 0 -150 356 AAACCCC C6 156 0 0 506 0 -150 356 AAADDDD C7 1450 -50 0 1900 -50 0 1850 AAADDDD C8 450 0 0 1900 -50 0 1850 AAAEEEE C9 24500 -100 0 24500 -100 - -

With this I wanted to calculate the total as follows:

aggr( nodistinct sum(invoice), 'VAT number')+

aggr( nodistinct sum(creditnotes),'VAT number')+

aggr( nodistinct sum(payments),'VAT number')

All seems to be working fine, only the C9 with VAT Number AAAEEEE would not calculate the sum. Im not sure why. The only reason I can imagine is that the aggr() function is having troubles because the VAT only occurs ones and the value of payment is 0. Rather than being 0 the aggr() function gives out " -" wich is why the total can not be calculated. Im sure Im overseeing some simple thing here, but can’t seem to figure out what causes the problem.

Labels (3)

• ### General Question

2 Solutions

Accepted Solutions

May be you surround your expression with rangesum().

rangesum(

aggr( nodistinct sum(invoice), 'VAT number')+

aggr( nodistinct sum(creditnotes),'VAT number')+

aggr( nodistinct sum(payments),'VAT number')

)

Contributor II
Author

ok so I got it working by only applying rangesum to the payments !! many thanks !

3 Replies

May be you surround your expression with rangesum().

rangesum(

aggr( nodistinct sum(invoice), 'VAT number')+

aggr( nodistinct sum(creditnotes),'VAT number')+

aggr( nodistinct sum(payments),'VAT number')

)

Or may be rangesum(Column(4) +Column(5) + Column(6))

Contributor II
Author

ok so I got it working by only applying rangesum to the payments !! many thanks !