Dynamic formula for Debit and Credit Transactions

Question

I have attached this data set. In which I have to create a dynamic formula which adds Debit and credit balance every month.

This sheet will get updated monthly and columns will be added.

For example:

Column H+ Column I = 1 (New column name)

Column J + Column K = 2 (New column name)

Column L + Column M = 3 (New column name)

and so on....

Like this will have 12columns for entire financial year. Adding debits and credits dynamically.

Can someone help creating this? Is it possible in Alteryx?

Any help is much appreciated.

test file.xlsx

martinson · Accepted Answer

Hi @Meghnagupta this is absolutely possible in Alteryx. I have created an example workflow using the data you provided. The main idea is to transpose the data and then use the multi row formula to do the calculations before the data is cross tabed and can then be fitted back to the original data. This was a quick build so it is not optimal however I hope it gives you a good idea of how to approach it. The calculated columns have a suffix _3 and are placed at the end of the data.

Let me know if you have any other questions!

Cheers,

martinson