Determine Peak Account Balance

[#1]

Hi,

I'm looking to determine a peak balance per account when looking at data such as this:

  Account, TransDate, Amount

  A, 01/01/2016, 100

  A, 02/02/2016, 200

  A, 02/02/2016, 10

  A, 03/03/2016, 300

  A, 04/04/2016, -200

  A, 05/05/2016, 50

  A, 06/06/2016, -40

  ....

  B,.....

  C,...

So I've got transaction data for an account but I need to know what the peak running total ever got to for each account.

The report will be along the lines of this:

Account	Peak Balance
A	610
B	....
C	....
....

I can't use 'as-at' tables because in addition to the fields shown there will be others in my data, such as transaction types, paid-in-by, and other things that the users may filter on. 

Please note that this is for an account summary, one line per account, and I'll not be including the transaction date, so I can't use 'BEFORE' to work my way back up the transactions within the account.

I know I can get the amount each date but what I'm after is, in effect...

MAX (

  AGGR (

  SUM ( Amount "where transdate <= current for AGGR" )

  ,

  TransDate

  )

)

Thanks in advance.

[sunny_talwar]

Why can't you just use Sum(Amount) here?

[sunny_talwar]

My bad, try this may be

Max(Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), Account, TransDate))

Only thing to make sure is that your TransDate is sorted in ascending order from the script. or if you are using QV12 or above, you can use this approach

The sortable Aggr function is finally here!

[#4]

SUM(Amount) for account A would include all transactions - as I am not including the TransDate in the report.

What I'm after is the peak balance.

[sunny_talwar]

Yup, I figured it out... did you try my next suggestion?

[#6]

That next one looks promising.  I'll let you know!

Thanks Sunny.

[sunny_talwar]

Sounds good

[#8]

It works for some, not others, but I think I'm teetering on the brink of success!  The example I gave misses out the fact that some values might be null and there are a lot more dimensions involved, multiple transactions per day, and other twiddly bits.

Once I've nailed it and identified and squidged the anomalies, I'll update the thread with info on what can affect it working.

Thanks for your help Sunny.

[#9]

OK, Sunny's "Max(Aggr(RangeSum(Above(Sum(Amount), 0, RowNo())), Account, TransDate))" worked smashingly for the example that I provided.

I just needed to ensure that I'd added all other dimensions that could vary within the account and that was it.