Background
In my workflow I currently have these columns from the "input data" app from an excel spreadsheet:
...and these via the "formula" app from user interface drop down or calculations:
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.
^^ 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!
Solved! Go to Solution.
@Lauren I would try the multi-row formula tool as shown in the attached v11.0 workflow. I sorted the data to ensure it was in the correct order, and then I used the group by feature of the multi-row formula tool. Hopefully that points you in the right direction!
This is so helpful! How do I get it so I only see a status in the month of 5 though? You got it so that 3 is NODATA, but 4 still has Change/NoChange. I am confused where this is defined. What is "IsNull([Row-1: Program]) doing? Because the programs aren't going to be Null ever right? I appreciate the help.
So you only want to see the change when the month is the latest month?
i.e. for 5, but not for 4, even though 3 exists?
I want to display all of the data, but only see the Status change from 4 to 5 displayed in the row for month 5. (see the table in the initial question)
New solution based on that feedback...(see attached)
if isnull([Row-1:Program]) then NULL() elseif !isnull([Row+1:Program]) then NULL() elseif [Row-1:Performance Color] = [Performance Color] then "No Change" else "Change" endif
For each program-performance element
if the row before is null() i.e. the first row then NULL
else where the row after is not null (i.e. there is a further month) then NULL
else do the calculation.
Ben
Last question--
I am trying to troubleshoot because your solution does what I want mine to do, but I have other things going on in the workflow that I would not be able to post here. My values were all coming out as "No Change" and I think it's because I have a filter on in my workflow that filters on the current month. When I remove the filter, I get a "Change" or "No Change" for all months (not just month 5).
I am thinking about creating a variable that's the previous report month (aka if Report Month is 5, Previous Report Month would be 4) so that I can filter on current month and previous month maybe? But although I changed the name of a variable called [#1] to [Report Month], when I try to make a variable called [Prev Report Month] and write in the configuration "[#1]-1" I get an error that says it's invalid. They are both doubles. Any ideas?
Hi Lauren,
I appreciate you cannot share your problem entirely but could you perhaps share the example which is causing the error (maybe just use a text input with a couple of lines in)?
Ben