topic Re: Accumulate timestamp-wise and find max/min for each date in QlikView

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

Mon, 13 Oct 2014 08:24:14 GMT

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

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

anbu1984 — Mon, 13 Oct 2014 08:32:49 GMT

Is this your expected output?

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

Mon, 13 Oct 2014 08:40:21 GMT

Hi Anbu,

thanks for your answer.

However, to receive the balance we need to ackumulate the cash flows coming in/going out.

In other words CF1+CF2+CF3+CF4+CF5 and so on and so on. Then find the daily max/min.

Please find attached source data. I've done some calcs in there. Expected output as per below:

Max 1-Apr	504,573.27
Min 1-Apr	-349,900.00
Max 2-Apr	20,006,667.80
Min 2-Apr	-2,600,000.00

Thanks in advance,

Olle

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

Mon, 13 Oct 2014 08:42:13 GMT

And here comed the attachment!
//O

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

ashfaq_haseeb — Mon, 13 Oct 2014 08:49:55 GMT

Is this what you are looking at?

Regards

ASHFAQ

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

Mon, 13 Oct 2014 08:55:59 GMT

I'm afraid not Ashfac, please see the excel file to see what I'm looking for

Not lookin for the largest/smallest transactions. Rather the min/max running balance.

Kind Regards,

Olle

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

anbu1984 — Mon, 13 Oct 2014 09:21:05 GMT

Check this

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

Mon, 13 Oct 2014 13:49:31 GMT

Hi anbu!

Thanks for your answer. Your suggested solution works assuming the input data is sorted in the correct order, which is not the case for me unfortunatley. Pls see attached. I reloaded data using your app, but I sorted the input data on value instead of date, which messed up the numbers pretty bad.

I need the app to return the correct figures, no matter the input sort order, any ideas?

Best Regards,

Olle

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

anbu1984 — Mon, 13 Oct 2014 13:59:21 GMT

I still see my script ordered on date in the attached app. Can you post what you tried and expected output for loaded date

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

Mon, 13 Oct 2014 14:34:03 GMT

Sorry,

attached the wrong qv-file.

Here comes the correct one.

result should still be the same:

Max 1-Apr	504,573.27
Min 1-Apr	-349,900.00
Max 2-Apr	20,006,667.80
Min 2-Apr	-2,600,000.00

Thanks in advance,

Olle

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

anbu1984 — Tue, 14 Oct 2014 05:41:33 GMT

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;

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

Wed, 15 Oct 2014 14:52:08 GMT

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

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

anbu1984 — Wed, 15 Oct 2014 16:36:06 GMT

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?

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

Thu, 16 Oct 2014 07:07:33 GMT

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

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

Thu, 16 Oct 2014 07:16:02 GMT

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

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

anbu1984 — Thu, 16 Oct 2014 08:21:13 GMT

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;

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

Thu, 16 Oct 2014 09:01:38 GMT

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

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

anbu1984 — Thu, 16 Oct 2014 09:26:33 GMT

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

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

Thu, 16 Oct 2014 09:39:25 GMT

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

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

anbu1984 — Thu, 16 Oct 2014 09:56:14 GMT

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