Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I've been attempting to solve this problem and I'm coming up empty.
I have a fact table that contains what is essentially a daily balance (though only on dates where a transaction exists). When I display it with date as a dimension, of course I get the correct answers. Without date as a dimension, or in the total row of a straight table, what I really want is to get the last balance within the possible date range defined by the selections.
Each "account" has a different starting and ending date, and the client wants to be able to arbitrarily choose date ranges.
The expression I want would be something like sum(aggr(only({$<Date={'$(=max({$<Balance={">0"}>}Date))'}>}Balance),Account)). Unfortunately, that doesn't work because the dollar sign expansion for max date is only evaluated once and thus gives the max date for any account within the selection, not each individual account.
Thanks in advance for any help!
Figured it out:
=sum(aggr(Subfield(Concat(Distinct Balance,',',-num(Date)),',',1),Account))
So basically, we're making a comma-delimited list of all the individual balances in reverse date order, using Subfield to grab the first one (last one by time), and summing across all of those.
Figured it out:
=sum(aggr(Subfield(Concat(Distinct Balance,',',-num(Date)),',',1),Account))
So basically, we're making a comma-delimited list of all the individual balances in reverse date order, using Subfield to grab the first one (last one by time), and summing across all of those.