Writing lots of formula within alteryx

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!

Test File.yxmd

Workflow

Help

Accepted answers

flying008

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

录制_2024_03_21_14_02_07_828.gif

All comments

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

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.

	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

录制_2024_03_21_14_02_07_828.gif

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!

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.

Forex Database Alteryx.xlsx

Test Dashboard Workflow.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703