Budgeting: Totals by Month for Yearly Budget

Comments

8 comments

  • Rainer Grabowski

    Hi Beth,

    for a suggestion which makes sense it would be helpful if you could share screenshots of both apps to see their structure (the screenshots should show the fields needed for the calculation).
    Some questions:
    1. I assume the month budget fields are money or number fields - correct? 
    2. Are the apps where the monthly budgets are in and the Expense Reports app connected via a relationship field?
    3. Do you use Globiflow (it's included in the Podio Premium Licence)?

    Rainer

    1
    Comment actions Permalink
  • Beth Barbaglia

    Hi Rainer,

    I think I've narrowed it down to just 2 apps: Expense Reports and Expense Budgets.

    I have linked them with a relationship field in Expense Reports.

    I also added 2 category fields in Expense Report - "Year" and "Month" for the sole purpose of being able to do a conditional calculation in Expense Budgets.

    I'm thinking, for example, if I can make the "March Actual" calculation in Expense Budgets say "if Year = 2019 and Month = March, then calculate the sum, otherwise don't calculate a number." 

    That way, I'm hoping it'll only total the sum of Expense Report items that reference that Account Name if the Year = 2019 and Month = March.

    Would that work? 

    Here's a screenshot of both apps. Thank you! 

    0
    Comment actions Permalink
  • Andrew Wardle

    I think a formula like this in your Accounts app calculation field [March Actual] should give the figure you want. 

    var amounts = @All of Amount with nulls
    var years  = @All of Year with nulls
    var months  = @All of Month with nulls
    var total = 0;

    for(var i = 0; i < amounts.length ; i++) {
    if (years[i][0]==@Year && months[i][0]=="March") {
    total +=amounts[i][0]||0;
    }
    }
    total;

    If it works I must credit Rainer here who taught me to do these sorts of calculations!

    0
    Comment actions Permalink
  • Beth Barbaglia

    Hi Andrew,

    It alllllmost worked. I had to sub out "@year" for "2019" because it wasn't pulling up a variable and I need the calculation to be for the year. (was that wrong?)

    Now the equation is populating a 0 - which is closer than I've gotten! 😄

    I tested it on one account that should be totaling $294 based on an expense that was submitted with that account code but it's still saying $0. 

    Any insight?

    0
    Comment actions Permalink
  • Andrew Wardle

    If you remove

    if (years[i][0]==@Year && months[i][0]=="March") 

    do you get a number? If so then there's a problem with one of the two conditions.

    I assume the Marketing Account field in the lower app (Expenses?) is a direct reference to the Subscriptions Account shown above?

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Beth, 
    can you please share a screenshot of your calculation field and also a screenshot of the item where you tried it plus a screenshot of one of the related items (where we can see the fields amount, year, month). 

    Rainer

    0
    Comment actions Permalink
  • Beth Barbaglia

    Hi Rainer and Andrew, 

    Attached are the screenshots requested:

    The calculation

     

    The app the calculation is in

    The app the calculation is trying to total amounts from

    0
    Comment actions Permalink
  • Andrew Wardle

    Beth

    var years = @all of years with nulls

    not

    var years = @all of amount with nulls

    Does that fix it?

    Also, you can automate the production of the Notes entries using a calc field to create an html table

    See Rainer's previous post for details.

     

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk