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|
Is the some kind of aggr / range sum function that I should use or some kind of Do while ... loop
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
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.
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.
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
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?
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
If(LoadID = Previous(LoadID), RangeSum(Peek('CumVal'), value), value) as CumVal
Order By LoadID, event_order_id;
DROP Table Table;