Cumulative sum in right order

[laurilahti]

Hi all

I have table with values LoadID, value, event_order_id and I'm trying to have cumulative sum / loadid in correct order / cirteria as in the example below.

The calculation for the cumulative sum for LoadID 413465 should go like this.

when event_order_id = 1 the sum is value,

when event_order_id = 2 the sum is value of the id 1 + value of the id 2

when event_order_id = 3 the sum is value of the id 1 + value of the id 2 + value of the id 3

LoadID	value	event_order_id	cumulative sum / loadid
413465	11,845	1	11,845
413465	-0,336	2	11,509
413465	-11,509	3	0,000
413490	3,616	1	3,616
413490	8,125	2	11,741
413490	-6,982	3	4,759
413490	-1,143	4	3,616
413490	-3,616	5	0,000
413507	7,853	1	7,853
413507	1,259	2	9,112
413507	-1,259	3	7,853
413507	1,149	4	5,488
413507	-2,365	5	3,123
413507	-3,340	6	-0,217
413507	-2,217	7	-2,434
413507	-1,080	8	-3,514

Is the some kind of aggr / range sum function that I should use or some kind of Do while ... loop

[sunny_talwar]

Is this you need in the script or front end of the application?

[pradosh_thakur]

Hi

rangesum(above(aggr(sum(value),event_order_id,LoadID),0,rowno())) this as the expression.

i guess your calculation is a little wrong here for the for 413507

edit : attache the .qvf as well

.

regards

Pradosh

[sunny_talwar]

What is the need for Aggr() if you are using the same dimension? Are you using this for sorting? If that is what the goal was, I would do it like this

Aggr(RangeSum(Above(Sum(value), 0, RowNo())), event_order_id, LoadID)

This way you are sorting the RangeSum(Above()), instead of just the Sum() function which doesn't really need any sorting.

[pradosh_thakur]

thank you sunny for your insight.Must have been unnecessarily used when started solving it. I will surely keep this in mind. Now i understand that rangesum(above(sum(value),0,rowno())) will do the trick and no aggr() is really needed.

regards

Pradosh

[sunny_talwar]

Yup because sorting can be handled by the chart properties... but in case there is a chart where it can't be, then you can use an over-arching Aggr() just like I showed above

[pradosh_thakur]

I was trying to take the calculation  into script. as you have mentioned earlier it can be done but i am having some hiccups doing that. Can you please let us know how to do this in script level?

[sunny_talwar]

Like this

Table:

LOAD * INLINE [

    LoadID, value, event_order_id

    413465, 11845, 1

    413465, -0336, 2

    413465, -11509, 3

    413490, 3616, 1

    413490, 8125, 2

    413490, -6982, 3

    413490, -1143, 4

    413490, -3616, 5

    413507, 7853, 1

    413507, 1259, 2

    413507, -1259, 3

    413507, 1149, 4

    413507, -2365, 5

    413507, -3340, 6

    413507, -2217, 7

    413507, -1080, 8

];

FinalTable:

LOAD *,

If(LoadID = Previous(LoadID), RangeSum(Peek('CumVal'), value), value) as CumVal

Resident Table

Order By LoadID, event_order_id;

DROP Table Table;

[laurilahti]

Just what I wanted. Awsome. Thanks Sunny.

[pradosh_thakur]

please close the thread by marking the answer correct and helpful.