Aggregation used as filter in Set Analysis

[Anonymous]

Hi everybody,

I know it´s a simple question but it seems like I can´t solve it myself.

I have some data that contains a list of invoices owned by a certain company. I want to be able to show only those companies whose invoices total less than a certain amount due, like 100.000, or range, like between 100.000 and 200.000.

I made an aggregation that works well (alone):

          Aggr(NODISTINCT Sum(TotalOwned), [Client])

where TotalOwned is the value field of the invoice. It groups all the invoices due by a client and shows the total owned.

But when I try to use that value as a filter on a set Analysis, I get nothing:

          Sum({$<{"$(=Aggr(Sum(TotalOwned),Client)) < 100000"}>} TotalOwned)

Any suggestion is welcomed.

Thanks in advance!

[sunny_talwar · Accepted solution]

Try this

Sum({<Client = {"=Sum(TotalOwned) < 100000"}>}TotalOwned)

[Anonymous]

It works! Thanks a lot, Sunny.

[kcvernon]

Hello

Can you help?

This is kind of what I am looking for but I want to add a date range in too.  I don't get the correct answer when I add in my date range, but as soon as I select date range on filters it works.  Even though I know my date syntax is correct as I use it elsewhere.

I'm looking for the count of customers who had a total sum of payments greater than 0 in a specific date range.

Count({<customer={"=aggr(Sum(payment),customer) > 0"}>}distinct customer)

Thanks