Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

flyingcheesehea
Contributor II

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
Highlighted
flyingcheesehea
Contributor II

Re: Latest balance within date range

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
Highlighted
flyingcheesehea
Contributor II

Re: Latest balance within date range

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