Calculate Remaining Budget by Month



  • Rainer Grabowski

    Hi Beth,

    do you have an own account code record for each year? Are the records in App 2 and 3 related to the corresponding account code year record?
    Do you want a calculation field Remaining Budget for each month or should they be all in one calculation field (as a list)? 

    For one month in one calc field and if account code is for one year and all records from app 2 and 3 are all the same year:

    var cceAm = @all of amount field in credit card expenses;
    var cceDate = @all of date field in credit card expenses;
    var ieAm = @all of amount field in invoice expenses;
    var ieDate = @all of date field in invoice expenses;

    var sum = @Jan;
    for(var i = 0; i < cceAm.length; i++){
    if(moment(cceDate[i]).format("MM") == "01"){
    sum -= Number(cceAm[i]) || 0; 

    for(var i = 0; i < ieAm.length; i++){
    if(moment(ieDate[i]).format("MM") == "01"){
    sum -= Number(ieAm[i]) || 0; 



    Comment actions Permalink
  • Beth Barbaglia

    Hi Rainer,

    Thanks for your response (and your brilliance).

    I ended up just making App 2 include both credit card expenses and invoice statements. For the sake of budgeting, it doesn't matter how the expense was paid for, just that the purchase was made. So now I only have App 1, with the budgets, and App 2, with the expenses.

    There's not a difference account code for each year - I would have to only take the sum from certain date ranges so that the expenses don't span across years and throw the totals off.

    Is there a way to do that without specifying actual date ranges? Probably not since a calculation field doesn't know what year it is, eh? I'm just thinking that if other Directors end up wanting to use this method, I would have to go in and manually adjust their dates every year.

    With the updated apps, here's an example of what I'm trying to make work:

    In App 1, this is the code for program supplies...01-03-22-2540

    In App 2, these are program supplies expenses that have been related to the above account code - $35 on Jan 3 2018, $35 on Jan 10, 2018 and $100 on Feb 16 2018.

    In App 1, I want one Money Field to be the actual budget for January. In a calculation field right below it, I want only expenses related to that account code that have a date between Jan 1-Jan 31 in the date field. Or better yet, the actual budget minus the expenses spent in just that month so the remaining number is how much we have left to spend that month.

    Jan budget: $1,500

    Jan remaining budget: $200


    Is that possible with the calculation you provided?

    Thanks so much for your help!

    Comment actions Permalink
  • Rainer Grabowski

    In principle you can take that calculation. But you have to tell the code for which year it should calculate so that the code can filter for the year.

    You have 3 options to do that:
    a) add a text field where you enter the year
    b) add a category field with categories e.g 2017, 2018,2019,2020
    c) add a date field "Year start date "
    The fields ust be updated manually each January - or it you have a Globiflow account they can be updated automatically by a flow.
    The disadvantage is that they must be updated each year, the advantage is that you can manually change the year, for example if you want to know the total expenses for Jan. last year.
    d) Create an app "Current Year", add one item with 1 text field only (the title field) and enter "2018". Then in your budget app add a relationship field which points to the app Current Year, make this field Required. So each new year you only have to update the year in one place (app Current Year) only for all budget items.
    The advantage (besides the fact to update the year in one place only): If you want you can sum the budgets and the remaining budgets for all budget items so you've an overview of total budgets/total remaining budgets.

    For a) add this variable to the code:
    var year = @text field;
    for b)
    var year = @category field;
    for c)
    var year = moment(@date field).format("YYYY");
    for d)
    var year = @all of title.toString();

    The code for Jan::

    var year = one of the 4 vars above;
    var month = year + "01"
    var sum = @Jan;
    var am = @all of amount field in xpenses;
    var date = @all of date field in expenses;
    for(var i = 0; i < am.length; i++){
    if(moment(date[i]).format("YYYYMM") == month){
    sum -= Number(am[i]) || 0; 


    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk