community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Calculation of forecast values by replicating excel formulae

Highlighted
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

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

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.

Meteor

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

Labels