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! 

[jdunkerley79 · Accepted solution]

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

[yash_290]

Hi! 

Thanks for your prompt response. Will try this in a couple of days and let you know if it worked.

However, "generate rows" tool is a good option to extend months further.....will start using this frequently.

[yash_290]

Thanks for the solution.....It worked! :)