Announcements
cancel
Showing results for
Did you mean:
Contributor II

Cumulative sum in right order

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

10 Replies
MVP

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

Master II

Hi

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

edit : attache the .qvf as well

.

regards

Learning never stops.
MVP

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

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

Master II

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

Learning never stops.
MVP

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

Master II

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?

Learning never stops.
MVP

Like this

Table:

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:

Resident Table