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

[Vinay_1012]

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.

[Qiu]

@Vinay_1012 

Can you also upload the data file to which your macro is liked to?

[rodalferreira]

Hey Vinay.

I understood you want to create a application that has a macro running things in the back. 

I think you have to add the interface with a parameter for your FY in this interface and make the macro runs based in this number. Saying that, you should think in use the interface tools in your app not in the macro..

I hope it helps.

Rodrigo

[danilang]

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

[Vinay_1012]

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

[Vinay_1012]

Also find attached the Input file for your perusal.

Thanks

vinay