Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi Column Formula

tsingh4
7 - Meteor

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

5 REPLIES 5
CharlieS
17 - Castor
17 - Castor

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
7 - Meteor

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
17 - Castor
17 - Castor

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
7 - Meteor

Thanks this is helpful

Thableaus
17 - Castor
17 - Castor

@MarqueeCrew 

 

A good use case of the Dynamic Formula macro.

 

Cheers,

Labels