Last value using AGGR or in the script

[johngouws]

I am stuck with an issue and hope someone can assist.

I have a data set with transactions for Accounts. There are many rows per account and they are timestamped. There is also a FLAG to define a active record.

What I need to do is sum the BALANCE for last record, based of the timestamp, for each account where the flag is Y.

Currently I am using "AGGR( Sum({<FLAG={'Y'},[DATE]={'$(=Max(DATE))'}>}BALANCE) ,ACCOUNT)" but that does not work.

I am also wondering if it is possible or 'better' to somehow create a flag in the script that could be used? There is a potential of the data volumes growing and I am afraid of performance in the future if AGGR is used in all the objects.

The INLINE table looks as such - but I also attach an example QV app:

LOAD * INLINE [

ACCOUNT, FLAG, REGION, BALANCE, DATE

85263101984, N, AA, 83421.79, 2017-10-31 18:27

85263101984, Y, AA, 83421.79, 2017-10-31 18:27

85263101984, N, AA, 83421.79, 2017-12-05 09:42

85263101984, Y, AA, 83421.79, 2017-12-05 09:42

85263101984, N, AA, 83421.79, 2017-12-05 09:50

85263101984, Y, AA, 83421.79, 2017-12-05 09:50

85263578945, Y, BB, 522467.02, 2017-12-12 12:35

85263578945, Y, BB, 522467.02, 2017-12-12 12:35

85263578945, N, BB, 522467.02, 2017-12-12 12:35

85263578945, Y, BB, 522467.02, 2017-12-15 11:18

85263578945, N, BB, 522467.02, 2017-12-15 11:18

];

Thank you for your assistance, so late in the week.

[m_woolf · Accepted solution]

Try the attached qvw:

[sunny_talwar]

Have you tried this using FirstSortedValue?

FirstSortedValue({<FLAG = {'Y'}>}BALANCE, -DATE)

[johngouws]

Both these options work and return the correct answer.

I see I cannot mark both answers correct, but I do like the solution from m\w because it takes place in the script.

Thank you also Sunny.

[johngouws]

Thanks a lot. This is exactly what I was looking for.