Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
laurilahti
Contributor
Contributor

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

LoadIDvalueevent_order_idcumulative sum / loadid
41346511,845111,845
413465-0,336211,509
413465-11,50930,000
4134903,61613,616
4134908,125211,741
413490-6,98234,759
413490-1,14343,616
413490-3,61650,000
4135077,85317,853
4135071,25929,112
413507-1,25937,853
4135071,14945,488
413507-2,36553,123
413507-3,3406-0,217
413507-2,2177-2,434
413507-1,0808-3,514

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

10 Replies
sunny_talwar

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

pradosh_thakur
Master II
Master II

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

.Capture.PNG

regards

Pradosh

Learning never stops.
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
Master II
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

Pradosh

Learning never stops.
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
Master II
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.
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
Contributor
Contributor
Author

Just what I wanted. Awsome. Thanks Sunny.

pradosh_thakur
Master II
Master II

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

Learning never stops.