# Re-bucket weekly sales data and calculate descriptive statistics

I have sales data in weekly buckets like this:

weekID product SoldQty
1      1       10
2      1       20
3      1       30
4      1       40
5      1       50
6      1       60
7      1       70

1      2       10
2      2       20


Calculating the standard deviation of weekly sales per product is pretty straightforward.

Now, I am asking the question: how do you calculate the standard deviation for the same data, but bi-weekly bucketed? x-weekly bucketed?

Question 2: is there an efficient algorithm for calculating it on weekly data instead of materializing the x-weekly combinations?

From the business side it means that I have various forecast horizons (1wk, 4 wks, 6wks...) per product. And I would like to build the confidence intervals for predictions of SoldQty within the forecast horizon.

It all seems very similar to the Safety Stock calculations from logistics, but I would like to be sure.

UPDATE:

Please consider there are multiple ways to re-bucket (re-combine) this data. I think that if the new bucket contains W weeks, there are W ways to re-bucket data. For an example of 2-weekly buckets.

Way 1:

bucket wkid prod sold
1      1    1    10
1      2    1    20
2      3    1    30
2      4    1    40
3      5    1    50
3      6    1    60
4      7    1    70
...


Way 2:

bucket wkid prod sold
1      1    1    10
2      2    1    20
2      3    1    30
3      4    1    40
3      5    1    50
4      6    1    60
4      7    1    70
...


Both options make sense business-wise. Essentially you need to calculate standard deviations between sales during fortnights. And, standard deviation in way 2 should be different from way 1.

If the week ID is given as you state, calculate a bucket variable $w_x=int(weekID/x)$. Then use a SQL statement to summarize the volume to levels of $w_x$.
• @user2516569, this can be handled with a shift $s$ in the weekID, so that $$w_x=int(\frac{weekID-s}{x})$$. The only issue this raises is that the first "bucket" might not represent a whole period, and probably would need to be discarded for the analysis.
• @user2516569 how is the complexity $O(a^W)$? I don't understand what you mean by "materialize." The summarization is linear with $a$.