Multi Column Formula

[tsingh4]

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")

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

[CharlieS · Accepted solution]

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. 

[tsingh4]

Thanks this is helpful

[Thableaus]

@MarqueeCrew 

A good use case of the Dynamic Formula macro.

Cheers,