Need some guidance/hints on an assignment

Question

First of all, apologies if I'm posting too often.  If there's a certain "cool-down" period that I need to adopt before posting again, please let me know.

I'm a bit stuck on something I'm finding very challenging.  I just learned (attempted to at least) the Multi-Row & Field tools, and I didn't have a grand ol' time with that endeavor.  I'm still trying to get my head round it - still trying to figure out expressions in the formula tool.

So, the instructions are as follows:

- Calculate [Costs] using the [Unit Cost], [Units Sold] and also the [Regional Uplift] multiplier
- Calculate [Revenue] by using [Units Sold] and applying the relevant Loyalty Bonus to the [RRP]
- Calculate [Profit]

Summarize your data to calculate [Total Profit] per franchise (retain the currency conversion amounts)

Then split into two workflow paths:

A) Using a combination of Sort and Multi-Row Formula assign the [Processing Priority] for each region.

Then output this file choosing a suitable name and file format.

B)  Sort the franchises by [Total Profit], and convert this value to the relevant currencies

One of the glaring issues is about this loyalty bonus.  I don't feel that enough information is given to make a determination about loyalty bonus.  There are 3 categories of loyalty bonus 1, 3, and 5 years.  Yet, the only piece of data we have on date is a month field.  How am I to figure this out if I don't have any information about the year?  Also, this bit about "applying the relevant Loyalty Bonus to the RRP," what do they want me to do?  Multiply some percentage to the RRP value and then add that to the revenue?  I'm not a finance guy, so I don't know anything about what to do with loyalty bonuses.

Also, can someone please tell me when to use a regular Formula tool vs the Multi-Field and Multi-Row formula tools?  I feel the answer is pretty straightforward, but it really isn't for me.

I'll include the workflow that I have, along with the solutions.  And as always, if it needs to be taken down, I'll do that right away.

q5.yxmd

sameersheikh · Answer

Thank you everyone for your responses.  Truth be told, even the instructor was a bit befuddled as to how they came up with their workflow with respect to the discounts.  I don't know, but I feel it's a poorly designed question.  While I have the solution, I still can't make heads or tails of it.

Hammad_Rashid · Answer

Let's break down the tasks and address the questions you have regarding loyalty bonus, the use of Formula tools, and Multi-Row & Multi-Field Formula tools in Alteryx.

Loyalty Bonus Calculation:

It seems like the Loyalty Bonus calculation is not explicitly defined in the instructions. However, you can make assumptions based on the available information. Since you have three categories of loyalty bonus (1, 3, and 5 years), you might assume that the Loyalty Bonus is based on the number of years the franchise has been active.

For example:

* If the "Loyalty Bonus" is 1 year, you might add 1% to the RRP.
* If it's 3 years, you might add 3%, and so on.

Please clarify this with your instructor or use your best judgment based on the information you have.

Formula Tool vs Multi-Row & Multi-Field Formula Tools:

* Formula Tool:

* Use the Formula tool when you need to perform calculations within a single record/row.
* It's suitable for tasks where each record is processed independently of others.

* Multi-Row Formula Tool:

* Use the Multi-Row Formula tool when you need to perform calculations that involve values from multiple rows.
* It's useful when you want to reference values from previous or future rows.

* Multi-Field Formula Tool:

* Use the Multi-Field Formula tool when you need to apply a formula to multiple fields simultaneously.
* It allows you to create expressions that operate on multiple fields at once.

Workflow Steps:

* Calculate [Costs], [Revenue], and [Profit]:

* Use a Formula tool for basic calculations like [Costs], [Revenue], and [Profit].

* Summarize Data for [Total Profit] per Franchise:

* Use a Summarize tool to group by franchise and calculate the sum of [Profit].

* Split Workflow:

* Use a Filter tool to split the workflow into two paths based on the condition you want (e.g., processing priority).

* Processing Priority:

* For calculating [Processing Priority], use a combination of Sort and Multi-Row Formula tools.
* Sort the data by the relevant fields.
* Use Multi-Row Formula to calculate the priority based on conditions.

* Output Processing Priority File:

* Use an Output Data tool to save the processed data with the assigned [Processing Priority].

* Sort Franchises by [Total Profit]:

* Use a Sort tool to sort the data by [Total Profit].

* Convert Profit to Relevant Currencies:

* Use a Formula tool or Multi-Field Formula tool to convert [Total Profit] to the relevant currencies.

* Output Sorted and Converted File:

* Use an Output Data tool to save the final processed data.

CoG · Answer

Here's a little information on each tool:

* Formula Tool: Allows you to create one new column or update a single column where each row is independent (i.e. when evaluating the formula for the 6-th row of the data, only information from other cells in the 6-th row is available.
* Multi-Row Tool: Also allows you to create only one new column or update a single column, with the exception that you can now use neighboring rows in the calculation (default is to use only immediate neighbors row-1 & row+1). Because the rows are dependent on one another, the multi-row tool is evaluated from top-to-bottom of the table. You can use Group-By fields to evaluate the data in chunks where each chunk is evaluated from top-to-bottom (This is useful for sub-indexing data)
* Multi-Field Tool: Allows you to update/create many columns, where each row, like the formula tool, is independent. You have access to a new parameter ([_CurrentField_]) which allows you to tailor your output based on each cells value in a row although you can also use "Original Fields" too, which are just the static columns from your data. In addition, you can set which Data Type the Tool will run on (e.g. You want to double the value of every numerical field, which wouldn't make sense to do to a string field)

Hope this helps provide some clarity. These tools make more sense, the more you use them. Try playing with all the interface tools (i.e. drop downs and check boxes in each too) and see how the output changes!