Writing lots of formula within alteryx

[TKM]

Hi everyone, I have a task where I am required to write multiple formula but I really do not want to.

I have a total of 12 months of data in 2023 and 2024 Jan to Dec (total of 24 columns) and I have to do the following formulas

1. Jan 23 vs Jan 24 in absolute value (12 additional columns)

2. Jan 23 vs Jan 24 in % value (12 additional columns)

3. Jan 23 vs Feb 23 in absolute value (11 additional columns) 

4. Jan 24 vs Feb 24 in absolute value (11 additional columns)

5. Jan 23 vs Feb 23 in % value (11 additional columns)

6. Jan 24 vs Feb 24 in % value (11 additional columns)

and come 2025 there will be a need to easily roll up the formula to create 25 vs 24.

I do not wish to write them individually and I can't think of an easy way to quickly do them up. Anyone have any ideas? Thanks a lot in advance!

[flying008]

Hi, @TKM 

Maybe you can upload some input sample data with header for try? 

[TKM]

Hi, sorry I forgotten to add the data, have added it into the main post! Thanks!

[flying008 · Accepted solution]

Hi, @TKM 

Finally, I was done with what seemed like a lot of work, but it wasn't that scary, as long as you figured out the logic.

- Due to the security policy, I can't upload the workflow file, so I'll show you the configuration animation.

- Since you use 2 basic data tables according to your needs, they are also attached below, and you can copy and use them.

- If you want to use data from another year such as 2025, you can add it to the source data first, and then replace the desired year with the Replace function of the Formula tool before the Append Fields tool, which is very simple.

	Call Field Name			Months table
Group	FieldName		ID	X	Y
1	Difference X 23 vs X 24		1	Jan	Feb
2	Difference X 23 vs X 24 (%)		2	Feb	Mar
3	Y 23 vs X 23 (Absolute)		3	Mar	Apr
4	Y 23 vs X 23 (%)		4	Apr	May
5	Y 24 vs X 24 (Absolute)		5	May	Jun
6	Y 24 vs X 24 (%)		6	Jun	Jul
			7	Jul	Aug
			8	Aug	Sep
			9	Sep	Oct
			10	Oct	Nov
			11	Nov	Dec
			12	Dec

[TKM]

Hi, thanks for your input. Took me awhile to understand the logic behind it and working it out to know that this really works and it saved a lot of time. Didnt knew that dynamic replace can be used to create formula. Thanks a lot! :D

[TKM]

hi @flying008, not too sure if you are able to help on the request, i have dropped my workflow and data within, i am almost done but my data is slightly more complex. as i simplified my scenario above. was wondering if you can take a look on how should i fix the logic as i cant get the dynamic rename to function per intended.