I have a CF report that counts monthly complaints, over the last 12 months, for 30 store locations. The report shows the 30 store locations in the first column, followed by 12 columns showing the last 12 months, with each cell (in the grid) showing the monthly complaint counts. This part is easy.
What I'm looking for is some mathematical way, using a function, etc., that can calculate A TREND "number" for each of the 30 locations, based on their monthly performance. It would be like having a CF list, or array, populated with 12 numbers, e.g., (54, 65, 108, 32, 55, 75, 99, etc.) for each location. No problem doing that.
But would like to know which locations are getting BETTER or WORSE, over time, based on the # of time periods (in this case 12 months, but understand more months or numbers produce better trend results) without having to chart the numbers, and insert a trend line.
What I'm hoping is that there is some function (perhaps a linear regression or other type), that can create A NUMBER or RATING, for each location. For example, "zero" would represent a FLAT trend. Anything higher than zerio, represents an increasing trend, anything lower than zero represents a decreasing trend. Then, I would like to sort on that "trend number" to find which stores are showing the most reductions in complaints, versus those showing increases. Or more importantly, identify locations showing a huge spike in complaints, or whatever it is that you are measuring.
Eventually, the report would simply display each location, and a trend number that can be sorted, to allow region managers to quickly identify locations with increasing or decreasing trends, or perform additional research when a large spike is seen at a specific location.
One thought was to calculate an average (a mean or median) and then standard deviation. But was wondering if there is some function or method that is available in CF, to calculate this "rating number" without having to do a lot of manual math.
Appreciate any help, advice or info to get in the right direction, and hopefully can take it from there. Thank you.