14 Replies Latest reply on Jun 17, 2014 1:18 PM by EMPCREATE

# how to "carry over" and calculate YTD fields for 12 months

Need to calculate fields then carry them over to next month and total them. Example:  JAN: "food"= \$100  YTD =\$100  FEB: "food"=\$100 therefore YTD2 would be \$200. But I have 7 fields that need to be added for YTD2-Total Net Sales as well...And I need to do this for all 12 months of the year.

Your help would be much appreciated...

Thanks,

Mike

• ###### 1. Re: how to "carry over" and calculate YTD fields for 12 months

If I understand correctly, you don't need a script for this.  In the calculate tab of the YTD field there is a option to make calculations of other fields.  So just select "Sum" and choose the fields you want to add.

• ###### 2. Re: how to "carry over" and calculate YTD fields for 12 months

I tied that but it's not carrying over to the next month. Can I send you the file to see if you can figure it out?

• ###### 3. Re: how to "carry over" and calculate YTD fields for 12 months

Let me understand

you have a serie of 12 fields for month expenses and besides that another serie of 12 YTD field for carring over previous months and finally, a single YTD2 field at the bottom?

• ###### 4. Re: how to "carry over" and calculate YTD fields for 12 months

|JAN|    |YTD|      event.value = this.getField("JAN").value   (put this in custom calculation script)

|FEB|    |YTD2|    just add JAN and FEB

|MAR|   |YTD3|     Just add JAN, FEB, MAR

all of this in the YTD fields

• ###### 5. Re: how to "carry over" and calculate YTD fields for 12 months

Here's the file…I've only done 2 months to get it right first before I add the rest of the year…

• ###### 6. Re: how to "carry over" and calculate YTD fields for 12 months

You cannot attach file here, but you can post one on a web site (e.g., dropbox.com) and post a link here.

• ###### 8. Re: how to "carry over" and calculate YTD fields for 12 months

I assume your file will have 12 pages, one for each month.

First of all, I see you made the value of "YTD1-1" to be the sum of itself and "CM1-1".  This does not make sense.  Since it is the first month of the year, monthly sales and YTD sales should be equal right?  You need to put this script instead in calculate of "YTD1-1":

//repeat for all seven fields of the first month.

event.value = this.getField("CM1-1").value;

From there, totat Net Sales fields will just add the 7 fields above them.

Now, when creating the february page, you want "YTD2-1" which is soft serve sales to be the sum of "CM2-1" (this months) and "YTD1-1" which is LAST MONTH'S YTD sales.  and so on for every month.  This month's sales + last month's YTD sales.

I see some problems with your file.  First of all, The name of your fields are unconsistant and it gets messy.  Use identation.  All January fields should be named JAN.SOMETHING.  Better yet, they should be named 1.something.something as it is the first month of the year.

inserting scripts the way I describe earlier will take forever since you will need to change scripts in every fields.  With a numerical pattern, you can easily add fields with a loop such as:

var cName = event.target.name  //isolating the name of the field I'm working on which is number.something.something

var cArray = cName.split(".")  // Creating an array removing the "."  [number, something, something]

var cMonth = Number(cArray[0])  //isolating the first item of my array which is the string "number" and turning it into a numerial value

var cTotal = 0;

for (var i=1; i<=cMonth; i++){  //A loop that will run until it reaches the value of the month you are working on.

cTotal = cTotal + this.getField(i+".something").value;  //adds values

}

event.value = cTotal  displays the result

The script will be the same for every months, no matter which one it is since it is scripted to "detect" which month it is working on.

Hope it helps.  Don't hesitate if something is not clear enough

• ###### 9. Re: how to "carry over" and calculate YTD fields for 12 months

Thank you this is a big help! I'm new to these kind of calculations (scripting) so I might have bitten off more than I can chew…so the CM column will also carry over to it's corresponding YTD then to the rest of the months?

• ###### 10. Re: how to "carry over" and calculate YTD fields for 12 months

yes, it should.  Be sure to activate the console (ctrl+J) when developping a script so if any error is triggerred when testing everything, you will know WHEN it was triggered and might be able to isolate the problem.

If you want to check the value of something during a script, let's say I want to be sure that cMonth has the right value, get it to show its value at any moment by displaying it in the console:

//insert this script anywhere

console.println("the value of cMonth is"+cMonth);

• ###### 11. Re: how to "carry over" and calculate YTD fields for 12 months

This is way beyond my ability…would you be interested in setting this form up for me? Of course you would be reimbursed for your time….

• ###### 12. Re: how to "carry over" and calculate YTD fields for 12 months

I'd be glad to help you.  Let me see what I can do.  I'm not sure this forum's rules accept or not contract requests, lol.  I'll be setting this up a little later today.

• ###### 13. Re: how to "carry over" and calculate YTD fields for 12 months

That is correct…also the Make Check Payable needs a field…the green colored fields are there so she can adjust them but the store owner is not supposed to write in them….hence the note at the top….I tried to reply through the PM but it kept giving me an error message…