Alteryx Designer Discussions

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

Create a moving sum/total in Alteryx Designer

gflbrown
5 - Atom

Fellow Designers, I seek your help and advice please.

 

I'm looking to create a rolling/moving 3 month total.  I've found similar topics on the forum and via Google but I am struggling to achieve the desired output.  I keep ending up simply tripling the original columns!

 

To clarify, please see below sample input data and desired output data:

 

INITIAL INPUT:

 

IDMonthMeasure 1Measure 2
ABCDE01/01/20188496
ABCDE01/02/201884103
ABCDE01/03/20187889
ABCDE01/04/20187593
ABCDE01/05/201887101
ABCDE01/06/20186889
ABCDE01/07/201884103
ABCDE01/08/201890102
JKLMN01/01/20183650
JKLMN01/02/20184866
JKLMN01/03/20184863
JKLMN01/04/20185065
JKLMN01/05/20186480
JKLMN01/06/20185879
JKLMN01/07/20185375
JKLMN01/08/20184960
STUVW01/01/20182436
STUVW01/02/20182638
STUVW01/03/20183851
STUVW01/04/20183355
STUVW01/05/20183446
STUVW01/06/20182938
STUVW01/07/20184970
STUVW01/08/20185072

 

DESIRED OUTPUT:

 

IDMonthMeasure 1Measure 2Measure 1 Roll3MonthsMeasure 2 Roll3Months
ABCDE01/01/201884968496
ABCDE01/02/201891103175199
ABCDE01/03/20187889253288
ABCDE01/04/20187593244285
ABCDE01/05/201887101240283
ABCDE01/06/20186889230283
ABCDE01/07/201884103239293
ABCDE01/08/201890102242294
JKLMN01/01/201836503650
JKLMN01/02/2018486684116
JKLMN01/03/20184863132179
JKLMN01/04/20185065146194
JKLMN01/05/20186480162208
JKLMN01/06/20185879172224
JKLMN01/07/20185375175234
JKLMN01/08/20184960160214
STUVW01/01/201824362436
STUVW01/02/201826385074
STUVW01/03/2018385188125
STUVW01/04/2018335597144
STUVW01/05/20183446105152
STUVW01/06/2018293896139
STUVW01/07/20184970112154
STUVW01/08/20185072128180

 

 

What I am after is two additional columns (I have two metrics to keep a total of), where the additional columns is the sum of the respective original columns for that month and the two months prior (that is, a sum of the three months up to that month).

 

For sake of example, I started from January but would require January to include November and December from previous year also.  Would not want the IDs to be accidentally mixed either.

 

How might I do this?  I've tried multi-row formulas and the summarize tool, but I just can't hack it :(

 

Thanks all.

7 REPLIES 7
MarqueeCrew
20 - Arcturus
20 - Arcturus

Have you tried CReW Macros.  We have a Moving Summarize that should work for you.

 

Cheers!

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
gflbrown
5 - Atom

I had not tried the macro route, no.  I'm going to unashamedly admit that I've been using Alteryx for some basic data manipulation for a while now, but not veered into the wonders it might be able to do yet!!  I've yet to use any sort of macros yet, and honestly a bit nervous to do so!

 

Will give it a go though, thanks!  The fact that there's a macro for it suggests there isn't an elegant or simple solution for it?

jdunkerley79
ACE Emeritus
ACE Emeritus

I would probably go with @MarqueeCrew awesome CReW macros but if you want to do it from first principles

 

2018-10-25_20-17-20.png

Add a record id and then creating running totals

You can then create an offset and self join the data

Finally a formula tool to produce window.

 

 

MarqueeCrew
20 - Arcturus
20 - Arcturus

@gflbrown,

 

I wrote a similar macro to the one that I suggested from Adam.  His uses an iterative approach and I wanted to create the solution using "Generate Rows".  James has given you a very shiny set of tools to use as well.

 

Choose wisely and accept that there are many ways to solve a challenge with Alteryx.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
gflbrown
5 - Atom

The macro worked!  Thanks very much, very useful and will likely be using it a lot in the future.

 

I don't doubt there are many ways to achieve this, but I didn't think this challenge I had would be quite so tricky!

gflbrown
5 - Atom

@jdunkerley79 I appreciate you showing another way!  Thanks for your input...  I could follow and understand it, so may use it at some point!

 

Still interested in other ways of doing this though... keen to find the most elegant solution!

SayliD
5 - Atom

Hello

 

Firstly thank you for sharing the above solution. I am having same problem statement, I have weekly data and want to calculate moving sum for last 12 weeks on data grouped by three key fields. In below case I want to group my data by Name,Location and Source then calculate moving sum of value for last 12 weeks as shown in column Moving_Sum_Value (in week 14,15 and 16). The approach you shared seems to be working for me on sample of data but as i have really large data set it is not working.I also looked into one of the use case of Moving Summarize tool but but it was adding values of only one field sorted in order and no group by, frankly did not really got how to use the node to get what i needed.Can you suggest another approach to achieve desired result shown below with large data set.

WeekNameLocationsourceValueMoving_Sum_Value
2020031001ABCSYSTEM126 
2020041001ABCSYSTEM127 
2020051001ABCSYSTEM128 
2020061001ABCSYSTEM129 
2020071001ABCSYSTEM130 
2020081001ABCSYSTEM131 
2020091001ABCSYSTEM132 
2020101001ABCSYSTEM133 
2020111001ABCSYSTEM134 
2020121001ABCSYSTEM135 
2020131001ABCSYSTEM136 
2020141001ABCSYSTEM137378
2020151001ABCSYSTEM138390
2020161001ABCSYSTEM139402
2020032001PQRSYSTEM226 
2020042001PQRSYSTEM227 
2020052001PQRSYSTEM228 
2020062001PQRSYSTEM229 
2020072001PQRSYSTEM230 
2020082001PQRSYSTEM231 
2020092001PQRSYSTEM232 
2020102001PQRSYSTEM233 
2020112001PQRSYSTEM234 
2020122001PQRSYSTEM235 
2020132001PQRSYSTEM236 
2020142001PQRSYSTEM237378
2020152001PQRSYSTEM238390
2020162001PQRSYSTEM239402

 

Thanks in advance

Labels