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

[Luke_C · Accepted solution]

Hi @newtoAlteryx2023 

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

[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.