Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

laurilahti
New 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

Tags (1)
9 Replies
MVP
MVP

Re: Cumulative sum in right order

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

pradosh_thakur
Valued Contributor III

Re: Cumulative sum in right order

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

MVP
MVP

Re: Cumulative sum in right order

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
Valued Contributor III

Re: Cumulative sum in right order

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

MVP
MVP

Re: Cumulative sum in right order

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
Valued Contributor III

Re: Cumulative sum in right order

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?

MVP
MVP

Re: Cumulative sum in right order

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
New Contributor

Re: Cumulative sum in right order

Just what I wanted. Awsome. Thanks Sunny.

pradosh_thakur
Valued Contributor III

Re: Cumulative sum in right order

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