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.
I am in the middle of my project and stuck at a point where I need some assistance.
The project I'm working on follows the April-March calendar, i.e. April is the 1st month, May is the 2nd, son on & March is the 12th month of the fiscal year.
My data looks like:
As you can see the columns naming convention- Apr 20, May 20 and so on till Mar 20.(Note: 20 here is the fiscal year of the client), when it reaches Apr it changes to Apr21, May21 and so on (these are columns directly from the previous file, need to automate the insertion of months' columns going forward using Alteryx).
The need is to include (insert) all months of the fiscal year dynamically into my workflow, i.e. since we are in October - the ask is to insert columns from October through Mar (21 Fiscal year) into the workflow.
Example: Oct21, Nov21, Dec21, Jan21, Feb21, Mar21 to be included into the workflow.
Once we reach April. Apr22, May22, June22, and so on till Mar22 gets added to the workflow dynamically.
Please help me out with this! Thank you in advance!
There are potentially a few different ways to do this, but to do this dynamically and for the long term, I think it will be easiest if you have a reference/lookup table which contains your fiscal calendar and the actual calendar.
The two methods you can try are:
1) The Dynamic Select Tool - Here you can write a formula to select fields based on your expression.
2) Transpose & Cross Tab - Here you would transpose the data to get it into column format and then filter the name column based on your date logic which would only keep dates for the correct fiscal year then cross-tab the data back into the correct format.