Accumulate timestamp-wise and find max/min for each date

[#1]

Hi,

I need to ackumulate cashflows in time order to see the balance peaks and lows for each date.

Should be a rather simple exercise but for some reason I cannot get i right.

Any ideas?

Samle file attached.

Kind Regards,

Olle

[anbu1984]

Data should be ordered by timestamp and not by date.

Initial:

Load Amount,Timestamp#(Timestamp,'YYYY-MM-DD hh:mm:ss.fffff') As Timestamp,Date(Floor(Timestamp#(Timestamp,'YYYY-MM-DD hh:mm:ss.fffff'))) As Date;

LOAD Amount,

    Timestamp

FROM

[TestMaxMinBalance (1).xls]

(biff, embedded labels, table is [Report 1$]);

NoConcatenate

Final:

Load Date,Timestamp,Amount,If(Date <> Previous(Date),Amount,Amount+Previous(Amount)) As CumSum Resident Initial Order by Timestamp;

Drop table Initial;

[#3]

Hi Anbu!

This is really annoying, I misled you before. My formula in the excel spreadsheet was wrong.

Should be accumulating as per below:

Payments        Current Running Balance

Amount 1          Amount 1

Amount 2          Amount 1 + Amount 2

Amount 3          Amount 1 + Amount 2 + Amount 3

Amount 4          Amount 1 + Amount 2 + Amount 3 + Amount 4

Amount 5          Amount 1 + Amount 2 + Amount 3 + Amount 4 + Amount 5

The sorting now looks great btw!!

Sorry for beeing such a pain in the ***

Expected output is:

Max 1-Apr	-2,000,000.00
Min 1-Apr	-7,225,831.32
Max 2-Apr	15,391,764.45
Min 2-Apr	-7,320,609.04

Thanks in advance,

Olle

[anbu1984]

What is the formula to calculate Min and Max? First row has 2nd Apr but this value you listed as Min for 1st Apr.

-2,000,000.00

2014-04-02 07:52:20.156915

I see running total across the dates in your latest sheet. How did you select range first 100 rows for 1st Apr?

[#5]

Hi Anbu,

Im trying to find out the Min/Max of the accumulated values below (Running Balance).

So first I need to ackumulate the amounts in order, one by one I will add to the running balance throughout the day. And then I need to find out the maximum value. The maximum might have arised upon the first amount or the 4th for instance.

Payments        Current Running Balance (ackumulated values)

Amount 1          Amount 1

Amount 2          Amount 1 + Amount 2

Amount 3          Amount 1 + Amount 2 + Amount 3

Amount 4          Amount 1 + Amount 2 + Amount 3 + Amount 4

Amount 5          Amount 1 + Amount 2 + Amount 3 + Amount 4 + Amount 5

Please see the latest excel sheet, I've done all the calcs in there, inc the min/max

Kind Regards,

Olle

[#6]

hi Again Anbu,

now I see what you mean with the excel sheet.

I've now fixed that. First 100 rows is now for 1:st and then starting over with the 2nd.

Correct expected output should be:

Max 1-Apr	893,697.96
Min 1-Apr	-35,104.46
Max 2-Apr	20,043,007.03
Min 2-Apr	-135,847.67

Kind REgards,

Olle

[anbu1984 · Accepted solution]

Change in the script to get cumulative sum

Load Date,Timestamp,Amount,If(Date <> Previous(Date),Amount,Amount+Peek(CumSum)) As CumSum Resident Initial Order by Timestamp;

[#8]

Brilliant Anbu!

Thanks a lot.

Just one last question. Now that Im gonna implement your solution into my maste app.

What is the purpose of the second LOAD in the Intial table?

Best REgards,

Olle

[anbu1984]

It is called as Preceding load. Preceding load is used to create Date field

[#10]

I understand, however you are also including "Amount" in the preceding load?

[anbu1984]

If you use Preceding Load, then only the fields listed in Preceding Load gets loaded into table.