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

[marcus_sommer]

I think it could be possible to put all check-logic within the "Final output" column by using nested if-loops which check if "Cum distance1" < 5 < 10 < 15 ... Maybe this could be a bit simplified by dividing the "Cum distance1" with 5, for example ceil/floor("Cum distance1" / 5) and/or mod("Cum distance1", 5) = 0 or something similar.

- Marcus

[qliksus]

May be something like this . I will find  a better logic for finding the last row but as of now check whether this works

Script:

Subtotal:
LOAD *  INLINE [     
distance,Cumdistance1
1, 0
2, 3 
1, 4 
3, 7  
2, 9  
2, 11 
1, 12 
1, 13

]
;

let vrows = NoOfRows('Subtotal') ;

Subs1:

load distance,Cumdistance1  
,if( numsum(distance,Peek('dist'))<=5 ,numsum(distance,Peek('dist')),distance)   as dist
,if( numsum(distance,Peek('dist'))<=5  ,numsum(distance,Peek('dist')),'A'&RowNo())   as Flag ,
RowNo() as Row

Resident Subtotal
Order by 2 ;

Subs:
load  * ,

if( wildmatch(Flag,'*A*'), Peek(dist)
  , if( Row=$(vrows) and dist<=5  , dist  ,
     if( Row=$(vrows) and dist>5  , Peek(dist)

))) as Finop1

Resident Subs1

;

DROP Table Subtotal,Subs1 ;

[vinieme12]

See Attached QVW for reference

[#5]

Dear Susant,

Thank you for your swift reply.

Your script looks interesting, but not full 100 % solution.

See here the outcome:

Finop1 has not the correct row number, it should be put 1 step back (compare with ciolumn Desired output).

May be, there is a confusion with column Cumdistannce1:

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

.

The first row is set on zero, then start with cumulative calculation. So the '4' in the original table must be replaced by '3' as ibn line with columns

Cum distance2 and Cum distance3.

Nevertheless a good start!

Best regards,

Cornelis.

[vinieme12]

cornelis have you tried the above?

[#7]

Dear Vineeth,

Thank you for your contribution, I appreciate this very much.

I have looked at your proposal and initially, it works fine, but not for a long range:

The output is not correct anymore, unfortunately.

It looks like that the solution is not a dynamic one, but rather a static script.

The question is how to convert into dynamic script irrespective the number of observations in a list.

Best regards,

Cornelis.

[Anonymous]

Hi

why do not try in PIVOT tables like this ?!!!

if(rowno()>1,'',(Rangesum(above(sum({1} distance),0,1))  for                  Cum distance1

if(rowno()<3,'',(Rangesum(above(sum({1} distance),0,1))  for                       Output1

if(rowno()>3,'',(Rangesum(above(sum({1} distance),0,1))  for               Cum distance2

[effinty2112 · Accepted solution]

Hi Cornelis,

This script:

Data:

LOAD

RecNo() as RecordNum,

distance;

LOAD *  INLINE [     

distance

1

2 

1 

3  

2  

2 

1 

1

]

;

Let vNumRecords = NoOfRows('Data');

Let vCumSum = 0;

For i = 1 to $(vNumRecords)

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

  if $(vCumSum) >5 then

   SignificantRecords:

  LOAD

  $(i)-1 as RecordNum,

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

  AutoGenerate 1;

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

  End If;

Next i;

LOAD

  $(i)-1 as RecordNum,

  $(vCumSum) as [Final Output]

  AutoGenerate 1;

Gives this output in a tablebox:

RecordNum	Final Output
1
2
3	4
4
5	5
6
7
8	4

Cheers

Andrew

[vinieme12]

Yes, this was prepared assuming you have 8 rows per recordset, that's what it looked like with the sample given.

How do you decide the quartiles then?