If you don't have Advanced, a looping script can produce these dates.
If you don't have Advanced, a simple problem like your posted question could be difficult.
WITH "Advanced, this is an extremely simple problem to solve.
As Phil suggested, a simple looping script with a couple variables is all you need. Note, that I took a little extra care to make sure there wasn't a trailing comma.
This is a very quick example (literally about 8 minutes) with no code documentation (a no-no) and almost no testing (another no-no) so there could be some issues I'm not considering. One potential issue is that it's "Assumed" that the fields will be in ascending order in the code below. That "assumption" should also be checked in the script and correctly handled (not for an error reported back to the user!) so you still get the right range of years. The script should also check for empty fields and other potential boundary conditions.
In any case, this should give you some ideas.
HOPE THIS HELPS.
The situation you describe is essentially the same as this one but you only want the years to increment and not the days.
The possible solutions take the same form.
Other than a custom function or a scripted solution, you could also achieve the result with a direct calculation.
// list years between field 1 and field 2
// This formula accommodates 7 years. Add more lines to add more years.
[startYear = Year(field 1);
endYear = Year(field2);
d = endYear - startYear
If(d>1; "," & GetAsDate(startYear+1);"") &
If(d>2; "," & GetAsDate(startYear+2);"") &
If(d>3; "," & GetAsDate(startYear+3);"") &
If(d>4; "," & GetAsDate(startYear+4);"") &
If(d>5; "," & GetAsDate(startYear+5);"") &
If(d>6; "," & GetAsDate(startYear+6);"") &
// Add additional lines here copying the format above to the maximum you need.
If(d>0; "," & endYear)
Or you can make a more complicated calculation that is essentially unlimited in its ability to list years.
The CustomList() function by Agnès Barouh does not require recursion and can be used as a regular calculation to generate an arbitrarily long list of years.
To convert it to a non-Custom Function calculation, wrap it with a Let() and define the three parameter values as Let() variables.
Since the CustomList() function returns a ¶ separated list of values, wrap that in Substitute() to use commas instead of carriage returns.
Start = Year(field 1);
End = Year(field 2);
Function = "[n]"
... the full original CustomList() definition goes here
2 of 2 people found this helpful
This make lines dynamically (max about 400 years)
startY = Year ( field1 ) ;
endY = Year ( field2 ) ;
expr = "Y=" & startY & ";result=Y" & Case ( endY > startY ; Substitute ( 10 ^ ( endY - startY ) - 1 ; 9 ; ";Y=Y+1;result=result&"",""&Y" ) )
"Let([" & expr & "];result)"
Please respond if any of the suggested approaches here meets your needs or if you have additional questions, OK?
Multiple folks have now posted, but we have gotten no feedback from you if any of the suggested approaches fixes your issue.
I tried this but the field is showing 2.0172e + 27 instead of years but when I click on the field its showing all the years as per my requirement. How should I rectify that?
1 of 1 people found this helpful
Gary, is the calculation set to show TEXT or number?
My bad. Changed to Text.
1 of 2 people found this helpful
That code will probably work (I haven't tested it), but it's difficult to read and it will be difficult to maintain. I'm not finding fault with the author of the code or the quality of its results, just from a code-review perspective, it is difficult looking code.
Also, it appears to have a limitation: "This make lines dynamically (max about 400 years)"
In this case, I would use a script example, like the one I posted wrote, or better yet, add a quick method to a micro-service.
In Java, what you're trying to do is literally ONE LINE OF CODE and it's declarative not imperative (declarative => you define "what" you want to do (as with SQL) without having to specify "how" to do it, as in scripts or code).
And, we can just specify we want a "Set" as the output so we can easily eliminate duplicate years -- see example below.
Assume you have these dates:
[2017-04-17, 2018-04-17, 2019-04-17, 2020-04-17, 2021-04-17, 2022-04-17, 2023-04-17, 2024-04-17, 2025-04-17, 2017-04-17, 2026-04-17]
datesList.stream().map(s -> s.getYear()).collect(Collectors.toSet()));
Final Set: [2017, 2018, 2019, 2020, 2021, 2022, 2023, 2024, 2025, 2026]
This code is easily callable from FMP.
Just another option.
The code I posted directly above, assumed you had all the dates already gathered, which was not your requirement from your original posting. So, the code I posted directly above would get the years from that already-gathered date list, but it would not create the date list itself as my script example did. So, this posting is only to clarify (and hopefully not confuse).
I would still opt for code readability whether imperative (as with an FMP script) or declarative (as with SQL), however. Someday, you'll need to maintain that code. IMHO, FMP calculations are OK if short and easily readable. Since there's no debugging mechanism for FMP calculations, that's another reason I avoid CFs most of the time.
Hey It worked but i have question, what if i want my years in this format YYYY YYYY YYYY, instead of YYYY,YYYY,YYYY?
Sorry I got it now, I tried removing but instead I removed it with quotes. Didnt see there were two double quotes. I got it now.