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

running total next 12 months (inventory stock)

Kk_larsen
8 - Asteroid

Hi!

 

I have read a lot of post regarding 12 rolling for the previous 12 months but none whatsoever regarding the next 12 months. 

 

I would like to create a running total /moving total that sums the next 12 month all the time. 

 

Like the window sum does in tableau. 

 

I hope you can help!!

 

Example

 

Kk_larsen_1-1614169756454.png

 

Kk_larsen_0-1614169743117.png

 

8 REPLIES 8
pedrodrfaria
13 - Pulsar

Hi @Kk_larsen 

 

Using the Multirow you can create a calculation with the 12 rows below.

 

Please see attached for the workflow:

 

pedrodrfaria_0-1614170122684.png

 

Pedro.

 

messi007
15 - Aurora
15 - Aurora

@Kk_larsen,

 

Please see below:

messi007_0-1614170411818.png

 

Attached the workflow,

Hope this helps,

 

Regards

Kk_larsen
8 - Asteroid

That makes sense! Thanks alot @messi007 and @pedrodrfaria 

I have 1 additional question. I want to translate this equation from tableau: WINDOW_SUM(ABS(SUM(if [Type] = "Demand" then [Quantity] else 0 END)), 1, 12)

 

Is it still possible using the multi row formula? 

pedrodrfaria
13 - Pulsar

Hi @Kk_larsen 

 

You would then need to add the IF function

 

IF [Type] = 'Demand' then 

abs([Row+1:Quantity])+
abs([Row+2:Quantity])+
abs([Row+3:Quantity])+
abs([Row+4:Quantity])+
abs([Row+5:Quantity])+
abs([Row+6:Quantity])+
abs([Row+7:Quantity])+
abs([Row+8:Quantity])+
abs([Row+9:Quantity])+
abs([Row+10:Quantity])+
abs([Row+11:Quantity])+

abs([Row+12:Quantity])

else 0 endif

 

If you want to do the absolute value for each variable, just add ABS( ) to all the variables. If the absolute is for the output, just put the whole function inside of a ABS()

 

Pedro.

Qiu
20 - Arcturus
20 - Arcturus

@Kk_larsen 
Made something that you can calculate the running total for any amount months by changing one location in the workflow.

0224-Kk_larsen.PNG

 

Kk_larsen
8 - Asteroid

Thanks! @pedrodrfaria great help

 

That works!

 

I have one last question - not the total same but also a tableau translattion into alteryx. If I also want to do a running sum that cummulates the quantity from start to end how would you recommend doing that when it is conditioned on type?

 

RUNNING_SUM(SUM(if [Type] = "Demand" then [Quantity] else 0 END)+SUM(if [Type] = "Supply" or [Type] = "Planned supply" then [Quantity] else 0 END)+SUM(if [Type] = "Inventory" then [Quantity] else 0 END))

pedrodrfaria
13 - Pulsar

@Kk_larsen 

 

Attached a workflow that I believe should answer your question.

 

I used the filter before to filter out what was not Demand, Supply or Inventory. This will give you a good idea of how to do it. Most of the times in Alteryx we use more than one tool to get it done.

 

pedrodrfaria_0-1614174709830.png

 

SeanAdams
17 - Castor
17 - Castor

I addition to what folk have mentioned below - you can also do a time-series forecast since this is time-series data.

Well worth looking at the single-tool examples under help / sample workflows; or google for videos like this one: https://www.youtube.com/watch?v=abBzvDijEnM

The benefit of doing this with time-series is that there may be true seasonality in your data (e.g. every summer there are more holiday bookings) and time-series analysis takes this into account.

 

This video provides a brief tutorial of using Times Series tools on historical single family home sales and includes an overview on how to configure the following tools: Field Summary, ARIMA, ETS, TS Compare, and TS Forecast Data for this training can be downloaded at: ...
Labels