This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Welcome both to the community and to Alteryx! The first few days can be a little confusing so very glad that you reached out.
@Joe_Mako is right about the formula, and his solution is the most efficient - but depending on when your fiscal years start and end you may end up with the wrong year (e.g. some EU entities have an FY that starts after August Break)
For example - if your fiscal year is from 01 Feb to 01 Feb, then the first row in your data (FY17: Jan) is actually 01 Jan 2018, right?
So - taking this step by step - there are quicker ways to do this, but given that you're new I thought that it would help to explode this out.
- First strip the FY off the fiscal year to get you to a number using a formula tool. You can do this by using right([Fiscal Year],2)
- then add 20 to it, so you have 2017. this is also just a formula (you can use the same formula tool as the one above, just add another formula below. Formula would be setting [Fiscal Year] column, and would say '20'+[Fiscal Year]
Now you have 2017 in the FY column and Period says Jan
- then add a column called Day. Not strictly necessary but will help you see what's going on. Use the formula tool - set a new column called Day to have a value of 01.
Now you have 3 columns: 2017; Jan; 01
Now all you need to do is figure out which year this is. That's relatively easy to do with a quick table, or a formula. The thinking is that if your fiscal year starts in March, then Jan & Feb are in the next fiscal year.
So - if we created a table that had all the months, and the ones that push into next year have a flag - then we can update this..
Now we have correct year; month & day - all you need to do is use a formula to tool to get a date.
@aberry0613 - this is a long-form way of doing it, there are a lot of tricks to get round this quicker, but I figure that since you mentioned you were new, it was worth doing the long form so that you can see it, and as you become more comfortable you can compact this.
If this or @Joe_Mako's reply get you to the solution you need - would you mind marking one (or both if applicable)? If you still have challenges or questions - feel free to reply back on this thread with questions and updates and some data, and we'd be glad to help.