running total next 12 months (inventory stock)

[Kk_larsen]

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

[pedrodrfaria]

Hi @Kk_larsen 

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

Please see attached for the workflow:

Pedro.

[messi007]

@Kk_larsen,

Please see below:

Attached the workflow,

Hope this helps,

Regards

[Kk_larsen]

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]

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]

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

[Kk_larsen]

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]

@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.

[SeanAdams · Accepted solution]

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.