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.
headoffice invoices , headoffice creditnotes and headoffice payments are calculated as follows:
- 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.
