Started the workflow by transposing the data columns then I proceeded to apply the following treatments:
- sorted it ascending by RM Category, Year and Month
- Replaced Null() values with with the value on the previous row
- Used a Multi-Row Formula for creating the 3 months average, respectively the 6-month one
Great use of Multi-Row formula tools.
It's weird that the problem is giving you a hint: "For Values that do not exist, set values to closest valid row. " But the solution actually does not do that. It uses a zero for nulls instead. So I followed the hint and my response is slightly different from the solution.
I definitely learned that you can change the Num Rows in the Multi-Row Formula tool to play with a bigger selection of row, rather than the default 2.
I also used the Average( ) formula rather than adding and then dividing by 3 or 6...should do the same thing theoretically :)
I also didn't do the last transpose as I liked how my table looked better. It doesn't make sense to me to put the RM Category as a Value. What was the reason for that?