
How to Calculate Cumulative Sum
I have this info on my Table COST_W, I have the Week, the Cost by Week, but I need a Formula for calculate the Cumulative Sum.
On the Table EV_Management is where I charge the Cost by Activitie and by Week.
Week | Cost_Week | Cumulative_Cost |
14 | € 0.00 | € 0.00 |
15 | € 2,291.00 | € 2,291.00 |
16 | € 7,220.00 | € 9,511.00 |
17 | € 6,333.00 | € 15,844.00 |
18 | € 18,022.00 | € 33,866.00 |
19 | € 31,685.00 | € 65,551.00 |
20 | € 29,173.00 | € 94,724.00 |
21 | € 94,724.00 | |
22 | € 94,724.00 |
I saw a Formula on MySQL but I don't know how implement on Ninox
SELECT t.day,
t.rental_count,
@running_total:=@running_total + t.rental_count AS cumulative_sum
FROM
( SELECT
date(rental_date) as day,
count(rental_id) as rental_count
FROM rental
GROUP BY day ) t
JOIN (SELECT @running_total:=0) r
ORDER BY t.day;
Appreciate help if there a way to implent that on Ninox
-
I had worked something out along Fred's lines, which can be adapted to your needs. If your table looks like this
Where the closing balance is opening balance plus adding less subtracting. In your Trigger after update put this code
let a := this.Order; let b := last((select Table1)[Order < a] order by Order).ClosingBalance; for c in (select Table1)[Order >= a] order by Order do c.(OpeningBalance := b); b := c.ClosingBalance end
Regards John
-
Rafael said:
Hi Fred the Formula give me 0.00€So my first question is does the filter work properly? Like the last post we need to check to make sure you are getting the correct records.
let t := this; concat((select COSTO_W)[Week <= t.Week])
If that gives you the proper records then you can add the field and see if it pulls the correct data.
-
Rafael said:
the formula give me this result, All Id's on COST_WAre you in the COSTO_W table? Can you upload a copy of the DB?
-
Hi both, I hope you don't mind me saying bit I'm not sure it is a good idea to have a select statement in a formula field
let t := this; sum((select COST_W)[Week <= t.Week].Cost)
If you introduce a new record within the existing records, the later ones don't always cascade update
Plus there's the issue of multiple select statements in your code
Regards John