- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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
]
- Tags:
- aggr
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
That was it! Thanks so much!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
What would you suggest then on larger data sets?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yeah, we were thinking of that option using group by. Thanks!