2
$\begingroup$

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.

Thanks in advance for your suggestions!

Improve this question
$\endgroup$
0

1 Answer 1

Reset to default
1
$\begingroup$

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$.

Improve this answer
$\endgroup$
5
  • $\begingroup$ Hi Paul. I've discovered a nuance to this question that is not covered by your answer. Would appreciate if you had a look! $\endgroup$
    – BusinessAlchemist
    Commented Jan 23, 2017 at 8:04
  • $\begingroup$ @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. $\endgroup$
    – Paul
    Commented Jan 23, 2017 at 13:27
  • $\begingroup$ Thanks a lot Paul for your contribution. Your answer is clear. An issue for me here is that you still have to "materialize" both ways of grouping in order to perform the ultimate calculation. In other words, the computational complexity is still O (a^W), W being the length of a bucket. In my case W varies from 1 to 13 which is already high. I'm looking for an algorithm or approach that makes calculating st.dev. an O(a*W) task. Constantine. $\endgroup$
    – BusinessAlchemist
    Commented Jan 24, 2017 at 15:31
  • $\begingroup$ @user2516569 how is the complexity $O(a^W)$? I don't understand what you mean by "materialize." The summarization is linear with $a$. $\endgroup$
    – Paul
    Commented Jan 24, 2017 at 16:00
  • $\begingroup$ Man, you're right, the summation is indeed linear. By materialising I mean that before calculating the st.dev. I need to actually create W datasets out of the initial one (and store them in memory). This is already troublesome on a large initial dataset that I have. Can you think of an algorithm with a complexity that is independent of W? $\endgroup$
    – BusinessAlchemist
    Commented Feb 6, 2017 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.