For one of my project I need to calculate subtotal in ROW. I have spent 3 weeks figuring out a way in alteryx but nothing did not work. Please see below table. I need to calculate rows where there is X. Account20222021Interest Expense43531315Interest Income44323232Net InterestxxxxCommissions2324342Other income323322Other expense32325344Net incomexxxx I am thinking to have this table in a Horizontal format and do a normal formula tool calculation. Is it possible to convert the table within the alteryx AccountInterest ExpenseInterest IncomeNet InterestCommissionsOther incomeOther expenseNet income202243534432xx2323233232xx202113153232xx43423225344xx

@Vidhatri I updated the workflow to do this. It is really just another transpose and crosstab after the formula. I've highlighted the new portion in the green container. The reason I included the RecordID as part of the account was to be able to sort the line items in the required order. Once you flip them back, the sorting can become different. That record ID will allow you to sort in the desired order. You could also use the structure and join method I have in the second workflow. Transpose Accounts.yxmd

Calculating ROWs in alteryx

SahadhKuruniyan

For one of my project I need to calculate subtotal in ROW. I have spent 3 weeks figuring out a way in alteryx but nothing did not work. Please see below table. I need to calculate rows where there is X.

Account	2022	2021
Interest Expense	4353	1315
Interest Income	4432	3232
Net Interest	xx	xx
Commissions	232	4342
Other income	323	322
Other expense	3232	5344
Net income	xx	xx

I am thinking to have this table in a Horizontal format and do a normal formula tool calculation. Is it possible to convert the table within the alteryx

Account	Interest Expense	Interest Income	Net Interest	Commissions	Other income	Other expense	Net income
2022	4353	4432	xx	232	323	3232	xx
2021	1315	3232	xx	4342	322	5344	xx

Workflow

Accepted answers

All comments

jdminton

I've included your request in the attached workflow, but the container identified as Preferred will allow you to set up your structure and prevent you from manually needing to create formulas. You will likely need to update the Template input to fit your output desired, but it will save you tons of time in the long run.

Transpose Accounts.yxmd

nagakavyasri

Screenshot 2023-06-14 142332.png

Data1.yxmd

Vidhatri

Thank you for your input, it was really helpful. I tried replicating this on my workflow and used 'Formula' tool to get the row totals (i.e., Net Interest = Interest Revenue + Interest Expense)

However, I would like to know how can we get the data back into the original format after calculating the row totals i.e., horizontally?

I would like to see the output in the below format after the row totals (i.e., Net Interest) are calculated:

Account	Dec22_ytd	Dec22_qtd	Mar23_ytd	Mar22_ytd
Interest Expense	-10	-20	-30	-40
Interest Revenue	30	50	40	60
Net Interest	20	30	10	20

I have also attached the workflow screenshot as well.

Alterxy.PNG

flying008

Hi, @Vidhatri

无标题.png

Qiu

@SahadhKuruniyan
I answered a similar question earlier I think.
https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Complete-Table-Transpose/td-p/1155678

0629-SahadhKuruniyan.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703