Multi Column Formula

I have a dataset with year to date data (YTD) on monthly basis, now I want to dynamically create a month to date data columns. Essentially Feb (mtd) = Feb (YTD) - Jan (YTD)

I am using multirow column box to create expression

If left([_CurrentFieldName_],3) = "Jan" Then [_CurrentField_]
Elseif left([_CurrentFieldName_],3) = "Feb" Then contains([_CurrentFieldName_]],"Feb") - [_CurrentField_] contains([_CurrentFieldName_],"Jan")

Workflow

Accepted answers

CharlieS

Got it! Thanks for that great example!

To achieve the desired result, I suggest Transposing the data so a Multi-Row Formula tool can easily make the MTD calculation. Once that is done, I transformed the data back to the original form (Cross Tab tool using the RecordID I assigned earlier in the workflow.) and renamed the fields.

Check this out in the attached example workflow and let me know if you have any questions.

20191003-MTDCalculation.yxmd

All comments

CharlieS

It seems like transposing the data could help here, but I'm not 100% clear on your desired outcome.

Could you attach an example workflow or show a before/after example?

tsingh4

I have Column A and Column B, I want to create Column C ( Column A - Column . But I want to do it for many column based on the column name. In my case column names have months name such as jab, Feb, Mar

Book1_Sample.xlsx

Thableaus

@MarqueeCrew

A good use case of the Dynamic Formula macro.

Cheers,

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703