Alteryx designer Discussions

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

Average calculation conditional on Field Name

Highlighted
Meteoroid

Hi there,

 

I have the attached the excel file which represents profit generated with clients. What I need is always the average of the last three actual months. In the example, the 2018-Mar-FC is the first month with an forecasted number (FC=Forecast, ACT=Actual). In order to forecast, I would need to calculate the average of the last 3 actuals months (here in the example Dec17, Jan18 and Feb18. In the next month, the column for March will change to 2018-Mar-Act, hence I would need the average for 2018-Jan-act, 2018-Feb-act and 2018-Marc act etc. Any good ideas?

 

Thanks, SVM

Alteryx Partner

 

@svm  This example should move you towards a solution.  As you can see here I suggest that you separate the actual and forecast values and rotate the actuals data so you can use the MULTI ROW FORMULA tool.

 

2018-03-30_00003.png

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

I appreciate @derekbelyea's solution works perfectly but I just wanted to give you a tip on efficiency.

 

In the solution Derek has used the same transformation several times (multi row), against different columns. Whenever you find yourself doing this in most instances it is possible to work with your transposed data where all these values are in a single column and 'group by' the header name field, which means the caclulation is made against each of your 'columns' but in a single tool.

 

I've attached a version with this alternative stream.

 

FewTools.png

 

Ben 

Labels