Cumulative sum and select if cum sum <= 5 in a column

[#1]

Dear all,

In the script I'm trying to find a proper solution for calculation.

distance	Cum distance1	Output1	Cum distance2	Output2	Cum distance3	Output3	Final output
1	0	0	-				-
2	3	3	-				-
1	4	4	0	0			4
3	7		3	3			-
2	9		5	5	0	0	5
2	11		7		2	2	-
1	12		8		3	3	-
1	13		9		4	4	4

Column 'distance' contains the input value from a list.

Column Cum distance 1 calculates cumulative sum from column distance.

( distance - peek(distance)), 0), peek('Cum distance1'))as [Cum distance1]

Column Output1 must contain cumulative sum of maximum 5, so the value = 4.

The next step is to calculate cumulative sum of distance again but the third observation is converted in zero and regarded as new begin value (Cum distance 2). So, Cum distance 2 depends on the outcome of Output 1.

Again, Output 2 has a maximum 5 and agrees with the statement that cum sum must be <= 5.

Output 3 is the last loop with max cum sum = 4.

Finally, I want to get a column with only max cumulative sum that agrees with the requirement <=5 (column Final output).

Well ...    ... not very easy to program in script?

So my question: do you have a smart solution?

Hope that you find a solution.

Best regards,

Cornelis

[#2]

Dear Andrew,

Thank you for your reply. This is exactly what I'm looking for!

It works for all data ranges. apparently, you need a loop to calculate the cumulative sum.

Best regards,

Cornelis.

[#3]

Dear Vineeth,

Actually, the numbers of records vary, I have over 10,000 records and have decided to deliver a fraction of it to simplify.

Andrew Walker has delivered the solution and uses a loop (For i = 1 to $(vNumRecords)).

Thank you for your contribution, it is good to read that you have taken efforts to help me on way.

Best regards,

Cornelis.

[#4]

Dear Alluraiah,

I understand your idea, but the problem is that calculation of cumulative sum is a part of a large script and therefore a pivot requires a lot of handling to convert back in original state.

However, it is worthwhile to have a closer look at your proposal once I have the time.

Thank you for your idea,

Best regards,

Cornelis.

[#5]

Yes, the one that has demonstrated below by Andrew.

Thanks and best regards,

Cornelis

[effinty2112]

Hi Cornelis,

Glad it works for you and thanks for posing such a nice problem.

Kind regards

Andrew

[vinieme12]

great stuff effinty2112

[#8]

Dear Andrew,

I have closely looked and your offered script into details and wonder whether it can also be applied for conditional event:

The cumulative sum is only allowed IF column Class has value > 0. The values in column 'Cum output' is the desired result.

With other words: the calculation of cumulative sum must stop once the value of zero has been reached in column Class.

Is uppose that can be integrated in your existing script, but could you demonstrate me how to solve?

Best regards,

Cornelis

[effinty2112]

Hi Cornelis.

This script appears to do the trick:

Data:

LOAD

RecNo() as RecordNum,

Class,

Distance;

LOAD * INLINE [

    Class, Distance

    2, 2

    2, 3

    2, 2

    2, 1

    0, 2

    0, 1

    3, 2

    3, 2

    3, 1

    3, 5

    0, 2

    0, 1

    0, 3

    1, 2

    1, 2

];

Let vNumRecords = NoOfRows('Data');

Let vCumSum = 0;

For i = 1 to $(vNumRecords)

Let vClass = Peek('Class',$(i)-1,'Data');

Let vCumSum = $(vCumSum) + Peek('Distance',$(i)-1,'Data');

//If vCumSum >5 record prev values as Significant Record and carry remainder over into new value for vCumSum

//If vClass =0 record prev values as Significant Record and set VCumSum =0

if $(vCumSum) >5 or $(vClass) = 0 then

  if Peek('Class',$(i)-2,'Data') >0 then

  SignificantRecords:

  LOAD

  $(i)-1 as RecordNum,

  $(vCumSum) - Peek('Distance',$(i)-1,'Data') as [Final Output]

  AutoGenerate 1;

  END If;

  if  $(vClass) > 0 then

  Let vCumSum = Peek('Distance',$(i)-1,'Data');

  ELSE

  Let vCumSum = 0;

  End if;

  End If;

Next i;

If $(vClass)>0 then

LOAD

  $(vNumRecords) as RecordNum,

  $(vCumSum) as [Final Output]

  AutoGenerate 1;

End if

We get this:

RecordNum	Class	Distance	Final Output
1	2	2
2	2	3	5
3	2	2
4	2	1	3
5	0	2
6	0	1
7	3	2
8	3	2
9	3	1	5
10	3	5	5
11	0	2
12	0	1
13	0	3
14	1	2
15	1	2	4

Kind regards

Andrew

[#10]

Hello Andrew,

That is very fast and also impressive work!

The good thing to know is that you have delivered valueable knowledge how to handle conditional calculations in a column using loops.

This is really appreciated.

May be a good idea I (and also other users on this site) where we can find any source about this kind of script programming?

I do not expect too much, but I see this as a breakthrough in script programming for difficult data set.

Have a great day , again thanks.

Best regards,

Cornelis