2

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

20 replies

null
    • Fred
    • 2 yrs ago
    • Reported - view

    Maybe something like:

    let t := this;
    sum((select COSTO_W)[Week <= t.Week].Cost_Week)
    

    You want to find all records with where the value in Week is less than or equal to the value in Week of the current record. This is assuming that you only have 1 record per week.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      Fred 

      Hi Fred the Formula give me 0.00€

      I place the Formula on EV_Management is in field RED Name FredFormula.

      The other table is COST_W where I have all the cost by Week.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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 Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      John Halls Thanks John appreciate.👍

    • Fred
    • 2 yrs ago
    • Reported - view
    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 Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      Fred 

      the formula give me this result, All Id's on COST_W

    • Fred
    • 2 yrs ago
    • Reported - view
    Rafael said:
    the formula give me this result, All Id's on COST_W

     Are you in the COSTO_W table? Can you upload a copy of the DB?

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      Fred 

      The DB

      • Fred
      • 2 yrs ago
      • Reported - view

      Rafael you didn't upload the DB.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      Fred Yes Fred is EVM-7-1_Junio.Ninox

      Upload again

    • Fred
    • 2 yrs ago
    • Reported - view

    I put this in cost_by_week:

    let t := this;
    sum((select COST_W)[Week <= t.Week].Cost)
    

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      Fred 

      Yes Fred Work Great Work. 👋

      Thanks a lot

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      Fred

      Great Fred Now Have  the Cost by Week and the Cumulative Cost and can represent the Curve with the  Plan - Earned and Cumulative Cost

      Thanks Fred.

    • John_Halls
    • 2 yrs ago
    • Reported - view

    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

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      John Halls 

      Hi John, thanks for new ideas, so far it's working fine, if you have any better ideas you'll be welcome, I have no idea of programming, it's taken me a long time to get to where I am without the help of several of you. I tried with your idea but something doesn't work when I enter a value in OpeningBalance it disappears.  I appreciate your help Thank you very much

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      John Halls

      Hi again John, Early this morning on my mockup I was working, I have 9 cutoffdates in my DB, I do 18 cutoff and work fine in all. I will try again in afternoon.

      This is aleatory Ninox bug ? 

      • John_Halls
      • 2 yrs ago
      • Reported - view

      Rafael I don't think this is a bug, just a limitation. Ninox would grind to a halt if all select statements fired without some reason and so, in a formula field, it waits for a field to change in value. By inserting a new record that works for the record in question, but the records following on from this one haven't seen a field change and so the select statement doesn't fire.

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      John Halls Hi John, some technical for me 👍 maybe work for me because  each time I cutoff I delete ALL the Progress Table and generate all New one. I have Two button on Table CUTOFFDATE when finish update project each friday, 1fr delete de Progress Table and generate New one with the other button. With this Table I have the 3 Curve Plan, Real,  & Cost

    • John_Halls
    • 2 yrs ago
    • Reported - view

    Yes, that's why it works for you. It also works if you only add new records to the end of the list but not if you are inserting records or updating them

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 yrs ago
      • Reported - view

      John Halls Great , Thanks very interesting explanation John 

Content aside

  • Status Answered
  • 2 Likes
  • 2 yrs agoLast active
  • 20Replies
  • 526Views
  • 5 Following