I am looking for assistance in coding the following tables run rate based on sporadic weekly sales data. The data is available as daily sales volume by customer and by product
I'm not sure this is possible as the run rate would be required for each product customer combination by day but any alternative ideas on run rates for data sets of this size and complexity would be welcome.
Calc 1: starting point no prior data therefore no run rate
Calc 2: no promo sales so run rate is total of that week for std sales only
Calc 3 :both promo and std sales so total of both for that week = run rate
Calc 4: no Std sales so run rate is total of that week for promo sales only
Calc 5: no sales so last week containing sales would calculate as the run rate (so if no sales for two months then the sales from the last point of sale would be used)
My table below would therefore not contain columns for std and promo merely a run rate for each week for each product and customer.
|Product||Week||Std Sales||Promo Sales||Run Rate|