Select to view content in your preferred language

Calculating Year Field from Date for Fiscal Year

1413
9
Jump to solution
10-17-2023 10:30 AM
Tiff
by
Occasional Contributor III

I'm working on a survey where we want to calculate both calendar year and fiscal year. Calendar year is easily done, but say our fiscal year is from Oct 2023 to Oct 2024 and does not follow the calendar year. Is there an easy way to calculate this directly in the survey upon submission? Thank you!

0 Kudos
1 Solution

Accepted Solutions
JenniferAcunto
Esri Regular Contributor

You don't need JS to be able to calculate that. You can use an if statement in the calculation column to get there. 

if(int(format-date(${date1},'%n')) >= 10, int(format-date(${date1},'%Y')), (int(format-date(${date1},'%Y'))-1))

 

- Jen

View solution in original post

9 Replies
SteveCole
Frequent Contributor

You could use Arcade to look at the current month of the current year. If it's October or later, you would use the current year's value. If not, you would use the previous year's value:

var theDate = Date();
var theMonth = Month(theDate);
  
If(theMonth >= 9) {
 return Year(theDate);
 } else {
 return  Year(theDate)-1;
}

Note: the Month function returns a value from 0-11 so you look for a value of 9 or greater

0 Kudos
Tiff
by
Occasional Contributor III

Thank you @SteveCole! Is there a way to do this dynamically in Survey123 as well upon the survey submission? If not, Arcade would work but to my knowledge that would require someone to edit the feature for that to populate, right? In the case that we'd want that hard coded into a field (rather than just in a pop-up).

0 Kudos
Raul
by
New Contributor III

I converted the logic you posted in arcade to javascipt, so OP can do the calculation on the S123 form using javascript fuctions.

function calculateFiscalYear(input_date, startMonth) {
	// Specify the month when the fical year starts
	// Use 1 for January all the way up to 12 for December
	const date = new Date(input_date);
	const curr_month = date.getMonth() +1;
	
	if(curr_month >= startMonth) {
		return date.getFullYear();
	} else {
		return date.getFullYear() + 1;
	}
}

 

An on the calculation field use:
pulldata("@javascript", "functions.js", "calculateFiscalYear", ${<input_date>}, ${<startMonth>})

0 Kudos
Tiff
by
Occasional Contributor III

This is really neat, thank you @Raul! I don't think I have combined using javascript functions with Survey123 calculations. Do I need to put the the script somewhere in my Survey123 Connect to be able to reference it?

0 Kudos
Raul
by
New Contributor III

Yes, you need to use the scripts tab in survey123 connect (bottom right) to include javascript files in your survey.

You can get more details about how to use custom javascript functions in survey123 here:
https://doc.arcgis.com/en/survey123/desktop/create-surveys/pulldatajavascript.htm

0 Kudos
JenniferAcunto
Esri Regular Contributor

You might want to check out Extending Survey123 smart forms with custom JS functions. Note that you cannot use JS with public surveys. 

- Jen
0 Kudos
Tiff
by
Occasional Contributor III

Ahhh thank you both @Raul @JenniferAcunto . Unfortunately the survey is shared publicly as of right now, so Field Maps may be the best option for this purpose. 

0 Kudos
JenniferAcunto
Esri Regular Contributor

You don't need JS to be able to calculate that. You can use an if statement in the calculation column to get there. 

if(int(format-date(${date1},'%n')) >= 10, int(format-date(${date1},'%Y')), (int(format-date(${date1},'%Y'))-1))

 

- Jen
Tiff
by
Occasional Contributor III

This worked really well! Thanks, and appreciate everyone else's input!

0 Kudos