Academic Term Function

[jt7Princeton]

Hi all!  I am really new at Alteryx so please forgive 

I am trying to figure out a way to create a workflow that I can reuse in other workflows that basically determines the current or most recent academic term.  I am guessing it might be as easy as using a formula expression but was wondering if there were more elegant ways to do this in Alteryx.

It would essentially be 

"If today's date is between September 1, 2016 and the end of the 1st full week of June 2017, the output would be 1617.  

 If today's date is between September 1, 2015 and the end of the 1st full week of June 2016, the output would be 1516.

 If today's date is between September 1, 2014 and the end of the 1st full week of June 2016, the output would be 1415"

...and so on

How should I go about this to maximize reusability in other workflows?

[gc]

If each instance of "today's date" fell in 1 and only one term (not sure if this is true), I'd consider setting up a Excel or CSV file with  1 column for "date" and another column for "term". Every date you need to be considered would have a row in this Excel or CSV file. You reuse this file with an Input tool in any workflow you need it.

Then join your data with this Excel/CSV file based on "date", then pull out "term" to add to your data. Something like that.

[NicoleJohnson · Accepted solution]

Could it be just as simple as the following?

1. Find the September Start Date of the given date using the following:

if [Field1]>=DateTimeFormat([Field1],"%Y")+"-09-01" then DateTimeFormat([Field1],"%Y")+"-09-01" else tostring(tonumber(DateTimeFormat([Field1],"%Y"))-1)+"-09-01" endif

2. Create the Academic year by taking the year of the September start date, and then the year following by adding 1 year:

tostring(DateTimeFormat([SeptemberStart],"%y"))+tostring(tonumber(DateTimeFormat([SeptemberStart],"%y"))+1)

The academic year is based on the September start date, right? So as long as you know the September start date, which you can find with the first formula above, you should be able to add to get to the second part of the academic year number by just adding 1 to the two-digit start year.

However, this ignores whether a date falls between the last full week of June and the start of the next academic year... so if it's critical to know those dates, let me know - I have a solution with a few more formulas that will get you to that one as well. :)

NJ

[jt7Princeton]

This is awesome.   I think your logic would work.  I figured someone would reply in such a way that made me see I was overthinking it.

[jt7Princeton]

Thanks gc.  I may end up going this way for accuracy in the end esp since the academic year format could change in the future.   I like the formula approach in this case.