Run Rate Algorithm

[#1]

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.

Essentially

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
1	wk3	200	100	0
1	wk4	300	0	300
1	wk5	100	200	300
1	wk6	200	0	200
1	wk7	100	100	200
1	Wk8	300	0	300
1	Wk9	0	0	300
2	wk4	450	0	450
2	wk5	150	300	450
2	wk6	300	0	300
2	wk7	150	150	300
2	Wk8	450	0	450
2	Wk9	0	0	450
3	wk3	600	300	0
3	wk4	900	0	900
3	wk5	300	600	900
3	wk6	600	0	600
3	wk7	300	300	600
3	Wk8	900	0	900
3	Wk9	0	0	900

[m_woolf · Accepted solution]

See the attached qvw.