To arrive at weighted average for the last 4 weeks by comparing prior year vs current year

Question

Hi Team,

I have a situation that i have date wise details for the last few years.  for the current month, say for example, if we are in April 25th, I have to forecast for the next few working days in April on the basis of weighted average calculation which is as below

20202021VarCurrent week17901770-1%Last but one week17101690-1%Last but 2 weeks1200161034%Last but 3 weeks1530190024%   13.92%

Details of working as well as data are there in the attached excel

The arrived weighted average of 13.92% is then applied to last year's actual to arrive at current year's forecast numbers.

Not sure how to do this in Alteryx. Can you please show some guidance on this?

Sample Data.xlsx

RaghavanR · Answer

Hi Team,

Any help by providing guidance on the above question would be highly appreciated?

Regards

Raghavan

RaghavanR · Answer

Hi @Qiu

Yes the weighted average will change with change in current week.

25th Apr date, I have given it as a current date as an example

however while giving the data I stopped with actuals until 18th April so that I will have good number of days to forecast in the month. Sorry that I was not clear.

Qiu · Answer

@RaghavanR

Can you clarify the by "current week" you mean the target day's week? or a week before?

Your excel calculation seems not consistent with the wording here.