How to make an expression in Multi row formula editable in an Alteryx Macro

Question

I am trying to make a Macro where i want to calculate Monthly numbers(MTD) from YTD(Year to date). I am using the expression "If contains([Name],"_01_") then [Value] else [value]-[Row-1:Value] endif" in the multi row formula to calculate the correct numbers.

What I want is, to put this workflow in a Macro. However i want this expression to become editable for users when they apply this macro in their own workflow, because now "01" represents January as the beginning of the FY(financial year)[Jan-Dec], however the FY beginning may differ( for example it could be July-Jun, where "01" should be editable to "07").

I am trying to use use some Interface tools and action tools but not able to make this expression editable.

Can anybody Please help?

Please find the workflow attached, where i am trying to do this using "listbox" and "Action tool".

Please guide.

New YTD to MTD.yxmc

Vinay_1012 · Answer

Also find attached the Input file for your perusal.

Thanks

vinay

Input.yxdb

Vinay_1012 · Answer

Hi Dani,

Thank you very much for your help and suggestions.

I tried the first way you suggested and i thinking its working. However when i am trying to share this macro to others in the team and when they are trying to include the macro in their workflow, it is giving a message that "File is not a valid macro". Could you please let me know how this could be fixed? Is there any specific way of saving macros when we use interface tools?

Kindly help.

Thanks

Vinay

YTD to MTD Macro.yxmc

danilang · Answer

Hi @Vinay_1012

You have two ways to accomplish this depending on your requirements.

1.  If you want the user to select only a single month then change your List Box to a Drop Down and change the action tool to update only the 01 part of the string.  A Drop Down will supply a single value to the Action Tool making the replacement easy

2.  If you want the user to select several months then you need to be more creative.  The final formula that you want in your Multi-Row would look something like this

If contains([Name],"_01_") or contains([Name],"_02_") then 
	[Value] 
else 
	[value]-[Row-1:Value] 
endif

with an OR clause for each of the months that the user selects.  Since your initial Formula is

If contains([Name],"_01_")  then

you need to get the List Box to generate the part in bold below and have the action tool replace the part in italics from above

If contains([Name],"_01_") or contains([Name],"_02_") then

To do this change generate Custom List Options in the List Box to

Start Text = Blank

Separator = _") or contains([Name],"_

End Text = Blank

Note I'm not sure if the Underscores in your initial formula are part of the actual field name, or just there to delimit the replacement string.  If they're just delimiters, remove them from the Separator option

The attached macro contains option 2.  Since I didn't have your input file you may have to play around with the actual syntax

Dan

New YTD to MTD.yxmc