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.

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

Vinay_1012
5 - Atom

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.

5 REPLIES 5
Qiu
20 - Arcturus
20 - Arcturus

@Vinay_1012 

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

rodalferreira
8 - Asteroid

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
19 - Altair
19 - Altair

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
5 - Atom

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?

 

vinay_1012_0-1612938605196.png

 

 

Kindly help.

 

Thanks

Vinay

Vinay_1012
5 - Atom

Also find attached the Input file for your perusal.

 

Thanks

vinay

Labels