Projecting data for a number of years using some parameters and conditions
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Preparation
- Workflow
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- 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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
