topic Re: Determine Peak Account Balance in QlikView

Determine Peak Account Balance

Thu, 25 May 2017 16:45:59 GMT

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.

Re: Determine Peak Account Balance

sunny_talwar — Thu, 25 May 2017 16:48:29 GMT

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

Re: Determine Peak Account Balance

sunny_talwar — Thu, 25 May 2017 16:50:26 GMT

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!

Re: Determine Peak Account Balance

Thu, 25 May 2017 16:56:06 GMT

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.

Re: Determine Peak Account Balance

sunny_talwar — Thu, 25 May 2017 16:57:58 GMT

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

Re: Determine Peak Account Balance

Thu, 25 May 2017 17:01:52 GMT

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

Thanks Sunny.

Re: Determine Peak Account Balance

sunny_talwar — Thu, 25 May 2017 17:04:17 GMT

Sounds good

Re: Determine Peak Account Balance

Fri, 26 May 2017 07:33:30 GMT

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.

Re: Determine Peak Account Balance

Fri, 26 May 2017 08:24:13 GMT

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.