Calculation of forecast values by replicating excel formulae

yash_290

I am supposed to calculate forecast values for some months based on the real values derived in excel. Excel formula has to be replicated in alteryx workflow for forecast months. Besides screenshot, PFA  sample excel file for reference . I also need to add months for forecast as decided by user input. Any help would be appreciated! 

Alteryx_Sample.xlsx

jdunkerley79

I suggest something like:

First, I parse the input into a table like:

TIme	Name	Value	Month
Monthly	Jun-18	1739805.45	2018-06-01
Monthly	Jul-18	1860353.76	2018-07-01

...

Next, I make a list of months (either forecast or actual).

Each time window is then computed on its own using a Multi Row formula to look back over the last 3 months. Each formula is ever so slightly different:

Monthly:

IIF(IsNull([Value]),([Row-3:Value]+[Row-2:Value]+[Row-1:Value])/3,[Value])

Annually:

IIF(IsNull([Value]),[Row-3:Value]+([Row-2:Value]+[Row-1:Value])/2,[Value])

Weekly:

IIF(IsNull([Value]),([Row-3:Value]+[Row-2:Value])*4,[Value])

Next I plug it back together using a Join Multiple tool in Record Position mode

The Total value is then computed using a normal formula tool.

Sample doing this attached - hopefully enough to get you going

Monthly Forecasts.yxmd