Trying to learn how to write a Macro

[Andrew418]

Hello,

I am using this quarantined time to learn more about Alteryx. I put together a workflow to calculate price, volume, and mix change for some sample sales data over a three year period. I am trying to make this workflow dynamic so when the periods change the calculations will still work. For example my test date I used 2016, 2017, and 2018 but next year it could be 2018, 2019, and 2020 or it could be 2018, 2019, and TTM Sep 2020. I have held off trying to incorporate the trailing twelve month calculation for now because I have received the follow error messages just using annual data.

When I try and test my macro using the same sample data I get some error messages saying "The field "Y1_Volume" is not contained in the record {Action=Sum}"

There was a step in my workflow where I used a select tool to change the column name from 2016 to Y1_Volume, my attempt to normalize the periods. However, it looks like that step in the workflow isn't working when I test the macro. 

Any ideas how to solve this issue?

[grossal]

Hi @Andrew418,

a Select-Tool will most likely cause issues when you try do make it as dynamic as possible. The Select Tool will look for the Column 2016 and try to rename it - but there is no column 2016 - so what should it do?

You could work with the Dynamic Rename Tool to accomplish a rename that won't throw errors when you input different columns.

Are you able to post the macro? This would help us to dig deeper into it.

Best

Alex

[Andrew418]

Thanks Betreff, I have attached the workflow for reference.

[grossal]

Hi @Andrew418,

I think I got you! I had to change only a little bit to make it work 😃

I have also tested it with new years in the attached workflow (fixed macro is also included).

Let me know what you think.

Best

Alex

[Andrew418]

Thanks Betreff, I appreciate the help here. I made the changes to my workflow but I still get an error when I try and test it by importing a data file and using the macro. I think it has something to do with the data type for the date field?

The attached file is the excel version of what I initially pasted in the macro as the sample data. When I use that file and attach to the marco I get error messages. The first error says field Y2_Volume is not contained in the record. 

[grossal · Accepted solution]

Your assumptions was right. Even though your Excel displayed it differently, your Date was already a DateTime formatted Cell and was recognized as a Date in Alteryx.

I have made changes to the Input in the Macro and removed the DateTime-Tool because it isn't needed anymore.

My tip for you is - don't copy paste the data into the macro Input - load it into the Input.

I also recommend to build the macro as a normal workflow first and then convert the Input/Output to Macro Input/Output.

I have attached the updated version. You might see a temporarily displayed error message before you run it the first time, but it should work now!

Best

Alex

[Andrew418]

Thank you.