Discussions

Expand all | Collapse all

Running Totals

  • 1.  Running Totals

    Posted 08-01-2020 23:01
    I have a child table with data in different years. I have a field called Total Dollars that totals the cash by year. I'd like to have a Running total of the cash each year.  How do I do that?

    ------------------------------
    Mike McCartney
    ------------------------------


  • 2.  RE: Running Totals

    Posted 08-01-2020 23:59
    I suggest trying a Summary Report Type, 
    Summarize Total Dollars (normal)
    Summarize Total Dollars (running totals)

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Running Totals

    Posted 08-02-2020 02:01
    I have the first one Summarize Total Dollars (normal) but don't know how to do the second one Summarize Total Dollars (running totals)

    ------------------------------
    Mike McCartney
    ------------------------------



  • 4.  RE: Running Totals

    Posted 08-02-2020 02:25
    I might not have asked the question right. I have a field called Total Dollars by Year (Formula Numeric) which calculates all the data in each season.  Can I create another Formula Numeric field that serves as a running total by Adding the previous year to all the data in that season?  This way, in my grid on the Parent Table, I can show Total Dollars by Year and Total Dollars earned through life of contract to that point.

    For example:
    Year   Total Dollars  Total Dollars Earned
    1           $100                  $100
    2           $150                  $250
    3           $125                  $375

    ------------------------------
    Mike McCartney
    ------------------------------



  • 5.  RE: Running Totals

    Posted 08-02-2020 03:19
    If you use the method which I suggested to make a summary report and use that an an embedded report of the children in the parent Record form, then you will get exactly what you asked for as a result. 

    You need to create a report of type Summary, not a summary field.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 6.  RE: Running Totals

    Posted 08-02-2020 09:52
    Got it - thanks.  I really appreciate you Mark.  Feels like I'm asking basic questions so thanks for your patience. 2 more questions. 
    1) Besides the grid, can I use the data in fields?  In other words, All Cash Earned thru 2 Years, All Cash Earned thru 3 years, etc.?  I want to run further reports with that data.
    2) How do I get rid of the 2 Totals table?  It's redundant in the grid.

    ------------------------------
    Mike McCartney
    ------------------------------



  • 7.  RE: Running Totals

    Posted 08-02-2020 10:26
    np,
    On a summary report, you can suppress the totals at the bottom as there is a checkbox setting for that on the report for that.

    Ah but now you have Part B to the question that you want to use the running totals in report or in other formulas, that is a more difficult question.

    The only way to do that really would be to Create a relationship between the Child table YEAR records and themselves.  Quick Base allows that.  The main thing to worry about there is the proper naming of he fields so you don't get your self confused.

    The issue will be how to connect the records so each child know which other record was its parent previous year.  ie how to make the daisy chain without breaking the connections.

    That can be done manually as you add a new child YEAR record, or it may be possible to create a Key field which is a concatenation of the [Related Parent] and the YEAR  and have them link up automatically. 

    To do that you would need to make a formula field to calculate that, then populate that into a data entry type "scalar" field and then change the Key field of the Child record from [Record ID#] to this new field.  Then going forward you would have a form rule populate the scalar Key field with the value on the Key field formula field.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Running Totals

    Posted 08-03-2020 19:06
    Thanks Mark. With a lot of trial and error, got it working!

    ------------------------------
    Mike McCartney
    ------------------------------