Background
In my workflow I currently have these columns from the "input data" app from an excel spreadsheet:
- [Program]
- [Month]
- [Year]
- [Recovery Date]
- [Performance Element]
- [Performance Rating]
...and these via the "formula" app from user interface drop down or calculations:
- [Report Month]
- [Report Year]
- [Report Date]
- [Previous Report Date]
- [Performance Color]
Note: [Month] and [Year] date back January 2016 so these go from (1/16, 2/16, 3/16,..., 3/18, 4/18, 5/18). Every month this excel spreadsheet is pulled and will insert the new month's data. I want to keep this data in the output to see changes over time, but only want to report the status of the [Report Date]. There are about 10 Performance Elements and 100 Programs for months and months of data.
Problem
I would like to loop through each [Performance Element] for each [Program] to determine if the [Performance Color] for a [Report Date] is the same as the [Performance Color] for the [Previous Report Date]. Basically I am wondering, How do I distinguish between the [Performance Color] from one month and the [Performance Color] from another month for different elements/programs without creating a ton of new variables?
Example
If I am pulling a report for May 2018, [Report Month] = 5, [Report Year] = 2018. In this example, the Status for Cost in May 2018 would say "No Change" because [Performance Color] went from Green to Green whereas Schedule would say "Change" because [Performance Color] went from Yellow to Green for May.
| Program | Month | Year | Performance Element | Performance Color | Status |
| A | 3 | 2018 | Cost | Green | (Null) |
| A | 3 | 2018 | Schedule | Yellow | (Null) |
| A | 4 | 2018 | Cost | Green | (Null) |
| A | 4 | 2018 | Schedule | Yellow | (Null) |
| A | 5 | 2018 | Cost | Green | No Change |
| A | 5 | 2018 | Schedule | Green | Change |
I really am just looking for a type of "For" or "Do While" loop here I think, but am confused with how Alteryx would do a loop without the ability in the formula app.
- For Each [Program]
- For Each [Performance Element]
- If [Performance Color].[Report Date]=[Performance Color].[Previous Report Date] Then
- [Status].[Report Date] = "No Change"
- Elseif [Performance Color].[Report Date]<>[Performance Color].[Previous Report Date] Then
- [Status].[Report Date] = "Change"
- Else
- Endif
^^ I know this is a made-up language but this would be how I would think of it if it helps anyone understand what I'm going for.
Thanks to anyone who tries to help!
