Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Latest balance within date range

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!

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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.

View solution in original post

1 Reply
Anonymous
Not applicable
Author

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.