Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
rohitraturi
New 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

Re: How to use Aggr() function

Why don't you try this

Dimension

[Account No]

Expression

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

Re: How to use Aggr() function

It gives an error as invalid dimension.

Re: How to use Aggr() function

What gives you an invalid dimension? Account No?

rohitraturi
New Contributor II

Re: How to use Aggr() function

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
New Contributor II

Re: How to use Aggr() function

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

Re: How to use Aggr() function

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
New Contributor II

Re: How to use Aggr() function

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

Re: How to use Aggr() function

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