topic Re: Aggr: nesting if logic in aggr function in App Development

Aggr: nesting if logic in aggr function

TonySchullo — Wed, 03 Jul 2024 18:13:45 GMT

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
]

Re: Aggr: nesting if logic in aggr function

chriscammers — Wed, 03 Jul 2024 19:30:28 GMT

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.

Re: Aggr: nesting if logic in aggr function

TonySchullo — Wed, 03 Jul 2024 21:20:42 GMT

That was it! Thanks so much!

Re: Aggr: nesting if logic in aggr function

TonySchullo — Wed, 03 Jul 2024 21:22:15 GMT

What would you suggest then on larger data sets?

Re: Aggr: nesting if logic in aggr function

chriscammers — Mon, 08 Jul 2024 13:00:13 GMT

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.

Re: Aggr: nesting if logic in aggr function

TonySchullo — Mon, 08 Jul 2024 13:40:29 GMT

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