Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Calculation of forecast values by replicating excel formulae

yash_290
7 - Meteor

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! 

 

Sample.JPG

3 REPLIES 3
jdunkerley79
ACE Emeritus
ACE Emeritus

I suggest something like:

2019-01-26_22-02-29.jpg

 

First, I parse the input into a table like:

TImeNameValueMonth
MonthlyJun-181739805.452018-06-01
MonthlyJul-181860353.762018-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
7 - Meteor

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
7 - Meteor

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

Labels