Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

YK_dup_378
7 - Meteor

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.

3 REPLIES 3
Joe_Mako
12 - Quasar

 

proj year.png

 

- 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
12 - Quasar

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.

 

config.png

 

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

YK_dup_378
7 - Meteor

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

Labels