Hi, Here is my dataset: NamePolicy noAccounting PeriodAmountABC1232023_01_31100ABC1232023_02_28500XYZ4562023_01_31200XYZ4562023_02_28700 I am transposing this table and calculating difference of amount between current month and previous month: NamePolicy no2023_01_312023_02_282023_01_31_Development2023_02_28_Development ABC1231005000400XYZ4562006000500 I am looking to get result as show in below table: How can I get from calculating difference of amount shown in above table to as shown in below table? thank you. NamePolicy noAccounting PeriodAmountDevelopmentABC1232023_01_311000ABC1232023_02_28500400XYZ4562023_01_312000XYZ4562023_02_28700500

Transpose 2 columns into 1 row

newtoAlteryx2023

Hi,

Here is my dataset:

Name	Policy no	Accounting Period	Amount
ABC	123	2023_01_31	100
ABC	123	2023_02_28	500
XYZ	456	2023_01_31	200
XYZ	456	2023_02_28	700

I am transposing this table and calculating difference of amount between current month and previous month:

Name	Policy no	2023_01_31	2023_02_28	2023_01_31_Development	2023_02_28_Development
ABC	123	100	500	0	400
XYZ	456	200	600	0	500

I am looking to get result as show in below table:

How can I get from calculating difference of amount shown in above table to as shown in below table? thank you.

Name	Policy no	Accounting Period	Amount	Development
ABC	123	2023_01_31	100	0
ABC	123	2023_02_28	500	400
XYZ	456	2023_01_31	200	0
XYZ	456	2023_02_28	700	500

Transformation

Accepted answers

Luke_C

Hi @newtoAlteryx2023

I wouldn't transpose. You can use the multi-row formula tool:

New Workflow3.yxmd

All comments

Luke_C

Hi @newtoAlteryx2023

I wouldn't transpose. You can use the multi-row formula tool:

New Workflow3.yxmd

SPetrie

I agree with @Luke_C , especially since I was about to respond with the same suggestion only 60 seconds slower

newtoAlteryx2023

I have a lot more Strings columns than what I have showed in the example. Using the suggested approach, I am getting Development as either 0 or same as the Amount.

Any idea what could be wrong?

Luke_C

Hi @newtoAlteryx2023

Check the grouping options to ensure they line up with what would currently make up a unique set of records to be analayzed.

SPetrie

Its hard to say without seeing the same thing you are.

Are you able to share some more example data that is closer to the real world data format you are having the issue with? That will help with tailoring a new formula or better solution.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

AdamR 335