Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Announcement | We'll be doing maintenance during the next 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!

Dynamic Forecast Advanced Help Please (Important !!)


Hi guys,


I really need your help on this. I have been stuck on this task for a while and I am running out of time ! I am still new to Alteryx so I am no expert by any means. I really hope you can crack this one! Please read carefully.


Context: we are creating a tool to help forecast company X's revenue. Every month, we have a data dump of the latest revenue and our model should be able to incorporate this data to provide a refined outcome. 


For simplicity, (raw data is way more complex), let us assume that the client gave us this data on April:

Project IDCustomer SegmentProductJan-18Feb-18Mar-18Apr-18
Project  1SMEIT6566668051738744
Project  2SMETelco6462341411649227
Project  3LargeTeclo1455794832224848
Project  4GovernmentIT6066933748085626
Project  5SMETelco9008889689694403


Accordingly, and via very complex equations and if statements we generate the following forecast till end of 2018

Project IDCustomer SegmentProductJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18
Project  1SMEIT656666805173874494763100317158657504993779383484
Project  2SMETelco646234141164922766251906886034796943181815945022
Project  3LargeTeclo145579483222484833058183302285426495101533568778
Project  4GovernmentIT606693374808562697082585765273571958743239214645
Project  5SMETelco900888968969440351949413786787563456424765958258


Next month we receive an extra field of the month's data dump 

Project IDCustomer SegmentProductJan-18Feb-18Mar-18Apr-18May-18
Project  1SMEIT65666680517387443795
Project  2SMETelco64623414116492272368
Project  3LargeTeclo14557948322248484563
Project  4GovernmentIT60669337480856265949
Project  5SMETelco90088896896944032202
Project 6SMETelco18635602531318754086


Accordingly, we do the calculations again to provide the following:

Project IDCustomer SegmentProductJan-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18
Project  1SMEIT656666805173874437955153377828835744386473543682
Project  2SMETelco646234141164922723687425998086877971440126908283
Project  3LargeTeclo145579483222484845634435753063059832705491151326
Project  4GovernmentIT606693374808562659491260338048066435456264054441
Project  5SMETelco900888968969440322022960287031207422408954632325
Project 6SMETelco186356025313187540865891813794539929799168405351



Here is the problem: It just cannot work on Alteryx


Any equation I do is conducted on specific field names eg (Jan+Feb+apr-May)*constant etc. In other words, when next month comes around, the equation should change to incorporate this month's data and the next field to be updated should be June instead of May. What's bad about alteryx is that the field names are hard coded in the equations. I cannot refer to them by any index etc. Even when adding a new field, the name has to be hard coded.


I am no expert on the software so I might be missing some key tools to solve the huge hassle I am facing,....


I really need your expertise on this as I am getting desperate!!!!!!


Thanks a lot!

Alteryx Certified Partner
Alteryx Certified Partner

Data transformation is key in scenarios like this where ordinality is important. If you use the transpose tool, you can then sort and prepare for the position-based calculation you're looking for.


Check out the attached workflow to show how I add a "Last 3 month" sum field to the input data. Let me know if this is heading in the right direction. 


Hi Charlie,


Thanks for taking the time to give it a shot.


Unfortunately, it doesn't help me for the following reasons:


1- In sample data, you are choosing N=3. This number cannot be dynamically changed based on what month the user is inputting the data in. 


2- Using the summarize function creates very limited solutions. In my cases, some of the equations I am using contain 10 nested if statements checking several parameters etc. before coming up with a revenue number. The summarize function only executes minor basic operations on the rows themselves.


Thanks again for your help. I really hope someone is able to crack this!


Hi @Joy1 


Can you post some sample data(input and desired output) and a few of your more complex equations?  I'm sure someone here will be able to come up with something to help you