I have transactional data that needs to be summed weekly. I would like to calculate the trend line slope based on weekly totals and by location, then rank the locations by the trend line slope. So I basically want to know which locations increase or decrease the most in terms of trend slope.
linest_m(total aggr(if(sum(Pieces),sum(Pieces)),Week, Location),Week)*Week
+linest_b(total aggr(if(sum(Pieces),sum(Pieces)),Week, Location),Week)
This works when creating the trend calculation for the entire data set and the first part of it for the slope overall. But when I create a table and bring in Location, for each Location the trend line calculation shows the same numbers for each location, which is the set of numbers for the entire data set, not the individual Locations.
Also, overall the forecast works for forecasting to following weeks, but when selecting a Location, the trend line only does the weeks data exist for, not doing the forecast at all.
In addition, I also need to be able to calculate weekly seasonality (actual weekly sum to trend point for the week in %) for forecasting, then use it for next years' forecast for that week.
I attached the concept with some sample data.
In the attached file, I used answers previously posted by
@Karthik Mungi
@John Witherspoon
Thanks
