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: 
rohitraturi
Contributor II
Contributor II

How to use Aggr() function

I have a table with the following columns.

Account Number - 1 per person

Charge Number - 1 account can have one/many charge numbers

Amount - Dollar amount associated with each charge number. It can be negative or positive.

 

I want to have only those account numbers where the sum(Amount) < 0 and also I want to create a separate column as account balance to show that sum(Amount) for each account.

 I have used the below-mentioned script but it is not giving the desired result.

=if(aggr(sum([Due]),[Account No])<0.00, [Account No])

=sum([Due])

8 Replies
sunny_talwar

Why don't you try this

Dimension

[Account No]

Expression

Sum({<[Account No] = {"=Sum([Due]) < 0"}>}[Due])
rohitraturi
Contributor II
Contributor II
Author

It gives an error as invalid dimension.

sunny_talwar

What gives you an invalid dimension? Account No?

rohitraturi
Contributor II
Contributor II
Author

In the set analysis, the query is shown as OK but the table is not generated and it throws an error as the invalid dimension.

rohitraturi
Contributor II
Contributor II
Author

I made some silly mistake initially but now it's working however not giving the desired result yet.

sunny_talwar

What are you getting and what is the desired result? Can you show an image of what you got and point out what is wrong with it?

rohitraturi
Contributor II
Contributor II
Author

Account number 257 should not be there as the sum(Due Amount) > 0 and also for account 187851 the account balance should be -0.01 (20.50-20.51).

sunny_talwar

Can you share the exact expressions for Account Balance and Due Amount?