Alteryx Designer Desktop Discussions
Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
Hi @Kyra88 ,
For the scenario manager instance, I think the answer is a batch macro. For goal seek I think the answer may be a batch macro on an iterative macro.
The difference between a batch macro and an iterative macro, is that the former runs for every value/set of values within a list that are fed to the macro, whilst the latter runs until a condition defined by the user is met.
For you requirement, I have created a batch macro that I will try to explain in as few words as possible how it works. It's not a finalised perfect product, but it can serve as a proof of concept of what Alteryx can achieve and may be tweaked to meet all requirements.
Step 1 : Generate a list of all the different combinations of company/market forecast rates.
To do that, I've used two "Generate Rows" tools, both generating rows from 1-10% (10 rows each, making 100 different combinations)
Step 2 : Create the process that wants to occur in each run in your batch macro.
Well generally the process I would want to happen in each run is described below:
My macro in each run will load the market/company sales tables, bring them in a format to forecast the values and then use a forecast ratio defined by the user, join them to get the total market share and restructure them in the old format for output.
Step 3 : Change the forecast ratio for company/market.
That happens via the tools with a black and white colour shown below, which in each run update the originally used forecast value.
What these tools do (and there are two of them in the workflow) is to update the ratio used in each run. The first will read from the Market forecast column (as that was shown in the first image attached to this post) and the second from the Company Forecast column. In the first run, both will feed in 0.01 (or 1%) because these are the values in the first record. The macro will run and will output Company/Market/Total Marketshare data out of the three outputs.
When that is done, the macro will run again for Market Forecast of 0.01 but a Company forecast of 0.02 and will create 3 new tables for Company/Market/Total Marketshare, which will be appended to the old ones. The process will go on until there are no more combinations left to be fed to the batch macro. In our instance, since there were 100 unique combinations, the macro will run 100 times.
Feel free to have a look at the workflow to better understand what's happening in each tool and please reach out if you have any further questions. The concept of a batch macro is a bit tricky to grasp, especially if you are fairly new Alteryx user.
To open a batch macro and explore it, just right-click on it and select open from the drop down
Regards,
Angelos
Hi @Kyra88 ,
Just saw this, on which version of Alteryx are you on? I can try and send over the correct version for you or you can try fixing it yourself.
All you have to do is open that zip file, and there you should have the batch macro and the workflow.
Then open a text editor (notepad) and drag your workflow in the notepad as shown below:
Change the version from 2020.3 to your version ("2019.2" for example) and then from " the top left of the notepad click on "File" > "Save".
Do the same for the batch macro. Now you should be able to open them in Alteryx and not receive that error message.
Let me know if that worked for you.
Cheers,
Angelos
Hi @Kyra88 ,
The issue for me was that when I was reading that excel table for Company A2, then for some reason Alteryx was picking up the last field called F15 and that was messing up the data structure after pivoting the data.
To face that, I configured the transpose tool so it only pivots fields that have Month as a name, so :
In the data column, I have unselected F15 and the last option. Now every new fields won't be pivoted and will be dropped, which can be a good thing because it gives you consistency, but also a potential source of error if your months change from Oct > Octob or October; Alteryx won't understand the difference and you will lose that column.
I went on and added some interface tools that allow you to select which file you want to connect to for each Company/Market, so you won't have to replace the files in the macro in the future. From the macro interface, you are prompted to connect to a file.
You can remove them if you don't find them useful.
Finally, if you want Company A2 to use all possible forecast (1-100%) that means that you will then create 10000 different combinations which should then be fed into the batch macro. That means that performance will be compromised to get all possible forecasts (it took 7 mins to run for my laptop)
Let me know if it worked for you. If you add many more companies then probably we should come up with an alternative plan, but for those 3 that should do the trick.
(PS you might have to change the version again for the attached items)
Regards,
Angelos
Thank you for the quick turnaround!
I just have a little issue. The Batch macro runs fine but when I run the workflow I getting the below error message.
Also quick question! Should there be a connection for the 3rd summary tool for the A2 output?
Thank you again for your time!
Kyra
Hi @Kyra88 ,
Don't know why this might be. There is this bottom part that is meant for company A2 and that has an output at the end of it so your macro should have 4 outputs instead of 3.
That error means something is wrong in the multi-row formula tool in that company A2 stream (the one after the Formula tool where we create the forecast for each run).
The error means that one of the fields used in that expression (Value or A2 forecast) is read as a string. Can you check if that is the case?
If you can't figure it out, you can add a select tool in between the formula tool and the multi-row formula tool and force the value and A2 forecast fields to take numeric values (set them to double)
Let me know if that worked for you.
