Dynamically inserting a new month to existing data (set)

Hi,

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!

Attaching the file for your reference.

Balances.xlsx

Accepted answers

Joe_Lipski

Thanks, I thought that would be the case. Please take a look at the attached and let me know if it helps.

Fiscal Years Report.yxmd

All comments

Joe_Lipski

Hi @RajatK

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.

Thanks,

Joe

RajatK

@Joe_Lipski , if you don't mind, could you PLEASE help me with the workflow for the same.

I'm new to Alteryx, and have been trying this for a while now.

Thank you in advance!!

Joe_Lipski

Does the real date of October 2021 = Fiscal year date October 2020?

RajatK

@Joe_Lipski , thanks for responding.

Fiscal Year 2020 would include the below months of the normal calendar:

April 2020, May 2020, June 2020, July 2020, Aug 2020, Sept 2020, Oct 2020, Nov 2020, December 2020, January 2021, February 2021 and March 2021.

And, so for fiscal year 2021 the months of the normal calendar would include:

April 2021, May 2021, June 2021, July 2021, Aug 2021, Sept 2021, Oct 2021 [we are currently here as the date stands], Nov 2021, December 2021, January 2022, February 2022 and March 2022.