Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Trick17
Contributor II
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.

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. 

Labels (3)
2 Solutions

Accepted Solutions
Digvijay_Singh

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')

)

View solution in original post

Trick17
Contributor II
Contributor II
Author

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

View solution in original post

3 Replies
Digvijay_Singh

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')

)

Digvijay_Singh

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

Trick17
Contributor II
Contributor II
Author

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