I have a detail which shows cities in the rows and months in the columns with sales for each location for each month. I would like to set up a 3 month average based on the month we are currently in, and give the file the ability to move forward automatically based on the month were in. I understand how to manually input an average, but am unable to figure out a solution to have it move forward each month when we change months.
Solved! Go to Solution.
Here is an example of what im working with... we are now in P6 so i would like an average of P3/P4/P5. but once we get to P7 P6 will be filled in via the input file, and then P7 would need to average P4/P5/P6.
Sales | P1 | P2 | P3 | P4 | P5 | ||||
florida | 100 | 104 | 108 | 112 | 116 | ||||
georgia | 101 | 105 | 109 | 113 | 117 | ||||
NC | 102 | 106 | 110 | 114 | 118 | ||||
SC | 103 | 107 | 111 | 115 | 119 | ||||
Hi @ryan14 ,
A transpose tool would work well for this. You can then sample the last 3 records (grouped by city), compute the average and then combine it back with the original data. I've mocked up a workflow below - let me know if this works for you or if you have additional questions.