Provide Recommendation based on Dataset automatically

[EC55]

Hello Community,

Do I have a challenge for you! 

I am working with an incredibly large data set (tens of thousands of rows). Below is a sample dataset. Basically I want to automatically provide a recommendation based on the spend patterns and current limits of the cardholder, and I don't want to have to manually calculate this for thousands of employees - I used to do this and it took me months of pain!!! 

I have managed to calculate most of the steps/columns below, but I am stuck on the two highlighted columns. How can I ask Alteryx to examine the current limit, the average monthly spend, take into consideration the highest spend in a month, and then return to me a recommendation for a limit that is commensurate with their current card activity? The recommendation column (when this was previously done manually and lived in Excel) was calculated using this formula: 

=IF(I4<200.99,"Cancel Card",IF(AND(I4>201,I4<500),"Review Requirement for Card", IF(AND((I4>500),((K4-L4)>1)), "Reduce Limit", IF(AND((I4>500),(K4-L4)<1), "No Action Required")))) which did save a boat load of time.

The problem I have is calculating the recommended limit!!! I am assuming there is a macro or something I could use, I just dont know what it would be. 

Name	Division	Total Spend (6 Months)	Start Date	Ave Mthly Spend	Highest Spend/mth	Monthly Credit Card Limit	Recommended limit	Recommendation	Potential Limit Reduction
Jane Smith	Department A	$           232.07	Sep 2015	$           38.68	$       385.00	$                   5,000.00	$                      -	Cancel Card	$        5,000.00
Bob Apple	Department B	$        3,485.88	Sep 2015	$        580.98	$   2,856.06	$                   5,000.00	$         5,000.00	No Action Required	$                    -
Betty Davis	Department C	$     57,411.17	May 2007	$     9,568.53	$ 15,407.19	$                 20,000.00	$       20,000.00	Travel	$                    -
Elton John	Department B	$        3,285.26	Apr 2013	$        547.54	$   2,674.56	$                 10,000.00	$         5,000.00	Reduce Limit	$        5,000.00
Dylan Bobby	Department C	$     11,789.25	Feb 2017	$     1,964.88	$   6,802.04	$                 10,000.00	$       10,000.00	No Action Required	$                    -
Francis Smith	Department A	$        3,172.14	May 2019	$        528.69	$   1,717.75	$                   2,000.00	$         2,000.00	No Action Required	$                    -

Relatively new user to Alteryx but a quick learner so keen to hear how I might be able to do this in Alteryx. 

TIA

Elyse 

[TomWelgemoed]

Hi @EC55 ,

I've attached an example outcome to you. I stress example as it's a bit of an arbitrary calculation as to how you would decide on the limit. I think that should be subject to internal knowledge and potential historic analysis on customers.

But for a general rule-of-thumb, you can simply use a formula like I've done in the attached and in the image below.

Separately, I wasn't sure about your Excel formula - the cell numbers didn't align with the sample provided. I've just written my own in Alteryx and you can adjust it as appropriate.

And I cleaned up the dollar signs & spaces in the data - otherwise the formula won't work.

Hope it helps.

Tom

[EC55]

Hello @TomWelgemoed 

You are AMAZING. That would have taken me days to figure out (only been using Alteryx for a few weeks). 

So a couple things: 

1. Good pick up on the excel formula - I deleted a column with sensitive info that was unrelated to this calculation step - and forgot that it would throw the columns out *facepalm* sorry! 

2. For my own learning, is the Multi-Field formula tool there to clean up the $ signs etc? I will need to study the formulas to understand them, as I have not used that tool before! 

3. The final formula that you use to calculate the recommendation, it works right up until the last < when it gives me the error "invalid type in operator >."

I had to update the fields to match the incoming data stream (the sample data I gave you I mocked up as the actual data is confidential) and this is the formula now: 

if [Average Monthly Spend]<=200.99
then 'Cancel Card'
elseif [Average Monthly Spend]>201 and [Average Monthly Spend]<=500
then 'Review Requirement for Card'
elseif [Total Spend (12 Months)]>500 and [Monthly Limit]-[Total Spend (12 Months)]>1
then 'Reduce Limit'
ELSEIF [Total Spend (12 Months)]>20000
THEN 'Travel'
else 'No action required'
endif

Can you see why it might be throwing that error? The red highlighted text is where the issue is. 

Thanks so much again, you have saved me so much time and headaches! 

Elyse 

[TomWelgemoed · Accepted solution]

Morning @EC55 (Elyse),

It's always really nice to hear when it does help someone out, so thanks for that. Alteryx is awesome, so stick with it!

To answer your questions:

1. The Multi-field formula tool is very powerful and once you get your head around it, is really easy. In essence it is what it says on the tin - it applies the same formula to multiple fields at the same time.Quick few pointers here:

• Instead of specifying the field name, you'll use [_CurrentField_], which is effectively the placeholder for the field(s) you want to change
• In the configuration pane, you need to select whether you want to change numeric/string/date etc fields in the drop down and then tick all the fields you want to apply it to
• I almost always forget this, but there is a checkbox where you can choose whether to output to new fields or update the existing ones. Usually, I want to update the existing ones, so I tend to untick it
• The formula will then apply to all fields that you apply it to. This is great when you want to get rid of say, NULLS, zero's or in this case, $ signs

2. I would love to update the formula my end and send it back to you, but because I used mock-up data (text), my data types is likely to be different than yours. So best I can do is point you to where the problem will be. That error you flag comes up when you are trying to do a formula on a field that has a type that doesn't work with it. For example, if you tried to multiply text or if you tried to do text functions on numbers etc. So most likely, 1 of the fields in the formula is a text type (as you're trying to do numeric functions on it). So check the Avg. Monthly Spend, Total Spend 12 mths & Monthly Limit fields and make sure they are numeric prior to entering the formula.

If that is still new to you, you can check & change the data type by putting a select tool just in front of the formula and check if it's either a string or numeric format. And the drop-down in the data type field allows you to change the type - that should solve your problem.

If it doesn't - you know where to find me!

Have a nice day,

Tom

[EC55]

Thank you Tom, got it working! Really appreciate it. 

Stay safe out there!

Elyse

[TomWelgemoed]

Thanks, you too Elyse.