Projecting data for a number of years using some parameters and conditions

[YK_dup_378]

Hello All,

I am a brand new Alteryx user (after years on Excel) and loving it!

I need your help on how to project data using some parameters and conditions for a certain number of years (and report values for each projected year). To simplify the problem here is what I need help doing in Alteryx:

• A record (a person) will have CURRENT AGE and SALARY. I have over 600K records.
• SALARY needs to be projected for 50 years (basically applying a salary increase each year for each record).
• CONDITIONAL STATEMENT: For any record salary projections should end if projected age (CURRENT AGE plus projected years) is more than 55.
• Finally for each projected year’s salary we are multiplying this by a number (say probability) which is based on CURRENT AGE plus projected number of years. These values can be obtained by looking up a PROBABILITY TABLE.
• I need to tabulate values of each projected year for each record.

I can easily do all of the above in Excel using conditional statements and VLOOKUP. I would build a parametrized conditional formula and drag it across the worksheet for 50 columns. Each cell would then get populated accordingly.

Now how do I even start all this Alteryx?? Please help!!

I have attached a sample Excel file with my Excel formulae built in for your reference.

Thanks so much!

Yusuf

PS: I could not find something similar to my problem when I searched the community – please forgive if this has been addressed somewhere before.

[Joe_Mako · Accepted solution]

- From "The Data", add a Record ID to uniquely identify each record, and ensure same sort at the end
- Generate Records 1 to 50 for each as the Projected Year
- From the "Probability Table", Transpose to make it tall, one record per age-year combination
- Select to set data type for Name and rename to Projected Year
- Join on Project Year
- Union Left and Joined output to retain all records
- Formula to calculate the new value (logic taken from the Excel file):

IF [Current Age]+[Projected Year]<=[User.TargetAge] THEN

[Salary]*
POW(1 + [User.SalaryEscalation],[Projected Year])*
IF IsNull([Value]) THEN 0 ELSE [Value] ENDIF

ELSE 0 ENDIF

- Cross Tab to reshape to desired structure with original sort

[Joe_Mako · Accepted solution]

I meant to mention that in the Workflow Configuration (click in canvas white space), and in the Workflow tab, I added two user constants for TargetAge and SalaryEscalation.

Please let me know if you would like additional details on anything here, thank you!

[YK_dup_378]

Hello Joe,

I just went thru your solution. Wow! This is fantastic. It does exactly what I had envisioned. Alteryx truly is a gem.

Thank you so much for your help! Both your workflow and explanations were easy to follow and understand. Thanks again.

Have a great weekend.

Yusuf