Post

2 followers Follow
0
Avatar

Calculate Remaining Budget by Month

I have 3 apps.

1 = account codes (it's how our company assigns expenses to each department)

2 = credit card expenses (has date field)

3 = invoice expenses (has date field)

 

In the account codes - I have 1 "Money field" for each month of the year so a Director could have their budget spread, i.e.

Jan: $1,500

Feb: $1,500

Mar: $1,500

Apr: $0

May: $0

 

I would like to calculate the remaining budget subtracting any related expenses to that account code. If I were doing this by the yearly budget, easy peasy - I'd just put the budget in one field and then make a calculation of (@budget - @all related expense) and get the remainder of the budget. 

What I can't figure out is the code to allow me to say (@jan budget - @all related expenses JUST in the month of January).

 

Any ideas? Thanks!

Beth Barbaglia

Please sign in to leave a comment.

3 comments

0
Avatar

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; 
}};

sum

Rainer

Rainer Grabowski 0 votes
0
Avatar

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!

Beth Barbaglia 0 votes
0
Avatar

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; 
}};
sum

Rainer

Rainer Grabowski 0 votes