Announcements
cancel
Showing results for
Did you mean:
Contributor II

## Aggr: nesting if logic in aggr function

I am trying to write an aggr expression that calculates taxable income at the account level, but displays it at the city/state level.  The rule is that if the sum for a given account is < 0, it is taxable, otherwise it's is not, so we set the taxable amount to zero (first two lines of chart below).  Taxable Amt = if(Sum(Amt)<0,Sum(Amt),0)

For my final display, I want to NOT include account in the chart (see bottom chart) but still do the aggr at the account level, imbedding the if statement within the aggr.

`aggr(if(Sum(Amt)<0,Sum(Amt),0),City,State,Account)`

However this is not working for me.

Thanks,

Tony

DATA:
City, State, Account, Amt
Miami, FL, 200001, 55.34
Miami, FL, 200002, -152.45
WPB, FL, 200020, -1335.00
WPB, FL, 200022, -765.88
]

Labels (2)

• ### General Question

1 Solution

Accepted Solutions
Partner - Specialist

The whole point of AGGR is to do multi-level aggregations.

Based on what you said I think just summing your original AGGR would do the trick

``Sum(aggr(if(Sum(Amt)<0,Sum(Amt),0),City,State,Account))``

Having said all that I would not use this regularly in apps with any significant volume of data. Aggr is notoriously bad for performance.

5 Replies
Partner - Specialist

The whole point of AGGR is to do multi-level aggregations.

Based on what you said I think just summing your original AGGR would do the trick

``Sum(aggr(if(Sum(Amt)<0,Sum(Amt),0),City,State,Account))``

Having said all that I would not use this regularly in apps with any significant volume of data. Aggr is notoriously bad for performance.

Contributor II
Author

That was it! Thanks so much!

Contributor II
Author

What would you suggest then on larger data sets?

Partner - Specialist

Creating a field in the data model that uses similar logic would be best. This becomes difficult if your measure is sensitive to user selections which is when AGGR is most applicable.

Contributor II
Author

Yeah, we were thinking of that option using group by.  Thanks!