hi designer I've a existing data set looks like below. some account has 2 years data, some not. AccountYearAccounting PeriodSum_Base Amount6182303000202001/2020750006182303000202002/2020650006182303000202003/2020550006182304000202101/2021450006182304000202102/2021350006182304000202103/2021250006182201000202001/2020300006182201000202101/2021400006182201000202002/2020500006182201000202102/2021400006182201000202003/2020500006182201000202103/202150000 and i want to transform to the below format.can anyone give me some ideas? thanks a lot Account01/202001/202101 Var02/202002/202102 Var03/202003/202103 Var6182303000750000-75000650000-65000550000-550006182304000045000450000350003500002500025000618220100030004000100050004000-1000500050000

Data wrangling

hi designer

I've a existing data set looks like below. some account has 2 years data, some not.

Account	Year	Accounting Period	Sum_Base Amount
6182303000	2020	01/2020	75000
6182303000	2020	02/2020	65000
6182303000	2020	03/2020	55000
6182304000	2021	01/2021	45000
6182304000	2021	02/2021	35000
6182304000	2021	03/2021	25000
6182201000	2020	01/2020	30000
6182201000	2021	01/2021	40000
6182201000	2020	02/2020	50000
6182201000	2021	02/2021	40000
6182201000	2020	03/2020	50000
6182201000	2021	03/2021	50000

and i want to transform to the below format.can anyone give me some ideas? thanks a lot

Account	01/2020	01/2021	01 Var	02/2020	02/2021	02 Var	03/2020	03/2021	03 Var
6182303000	75000	0	-75000	65000	0	-65000	55000	0	-55000
6182304000	0	45000	45000	0	35000	35000	0	25000	25000
6182201000	3000	4000	1000	5000	4000	-1000	5000	5000	0

Transformation

Preparation

Accepted answers

Qiu

@Haokun
It turns out to be a lengthy one. 😁

0322-Haokun.yxmd

All comments

Haokun

hi i dont want to create formula tool and select tool to manually create the formulas and re-name the header in different year. becoz the formula need to be looking at the headers and i have to re-update the formula and select tool to change to the correct header sequence. The ideal solution would be get the variance before the cross tab.

Qiu

@Haokun
It turns out to be a lengthy one. 😁

0322-Haokun.yxmd

Haokun

@Qiu

hiii master Qiu,

thanks for your workflow. i didn't use your workflow but your concept did inspire me alot. in the end, i use the GL and period to append to a master list and join back to the raw data, the transactions are failed to join, i will set the amount to 0 and union back to the raw data. so my raw data has all the period. after that i will use a multi row tool to compute the variance. i am not sure if you are able to visualize the flow.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703