Tiers - Each Year of Purchase Corresponding Output

[KaraRademacher]

I have a tier chart I am trying to apply to annual purchase numbers by participant by year.  I attached the tier chart and a sample of what I would like the output to look like.  The left side of has the actual purchases by year and the right side is the output (monthly benefit) based on the tier chart for the same periods.  How do I recreate this using alteryx?

[KaraRademacher]

The other option could be to do a formula for each year and add a column but this seems like it might be more effort.  Also I'm getting an error which is parse error at char (444): Malformed If Statement (Expression #1) when I try this route.

[Prometheus · Accepted solution]

@KaraRademacher I used an Append Fields tool to create records for all the tier levels for all the records on the original table (Left side of Tier Output.JPG). After that, I used conditional expressions to populated the monthly benefit fields. I then transposed the data, filtered out any records where Value was null, used a Cross Tab tool to re-pivot the data and create the table output you requested.

 

 

[KaraRademacher]

@Prometheus this solution worked, thank you!  I was able to see everything looked good with the browse and then output to excel.  Appreciate your help!