Aggr: nesting if logic in aggr function

[TonySchullo]

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.

Can someone please help?  Desired results shown in red in bottom chart.

Thanks,

Tony

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

[chriscammers · Accepted solution]

 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.

[TonySchullo]

That was it! Thanks so much!

[TonySchullo]

What would you suggest then on larger data sets?

[chriscammers]

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.

[TonySchullo]

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