Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: 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 !