Create a moving sum/total in Alteryx Designer

[gflbrown]

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:

ID	Month	Measure 1	Measure 2
ABCDE	01/01/2018	84	96
ABCDE	01/02/2018	84	103
ABCDE	01/03/2018	78	89
ABCDE	01/04/2018	75	93
ABCDE	01/05/2018	87	101
ABCDE	01/06/2018	68	89
ABCDE	01/07/2018	84	103
ABCDE	01/08/2018	90	102
JKLMN	01/01/2018	36	50
JKLMN	01/02/2018	48	66
JKLMN	01/03/2018	48	63
JKLMN	01/04/2018	50	65
JKLMN	01/05/2018	64	80
JKLMN	01/06/2018	58	79
JKLMN	01/07/2018	53	75
JKLMN	01/08/2018	49	60
STUVW	01/01/2018	24	36
STUVW	01/02/2018	26	38
STUVW	01/03/2018	38	51
STUVW	01/04/2018	33	55
STUVW	01/05/2018	34	46
STUVW	01/06/2018	29	38
STUVW	01/07/2018	49	70
STUVW	01/08/2018	50	72

DESIRED OUTPUT:

ID	Month	Measure 1	Measure 2	Measure 1 Roll3Months	Measure 2 Roll3Months
ABCDE	01/01/2018	84	96	84	96
ABCDE	01/02/2018	91	103	175	199
ABCDE	01/03/2018	78	89	253	288
ABCDE	01/04/2018	75	93	244	285
ABCDE	01/05/2018	87	101	240	283
ABCDE	01/06/2018	68	89	230	283
ABCDE	01/07/2018	84	103	239	293
ABCDE	01/08/2018	90	102	242	294
JKLMN	01/01/2018	36	50	36	50
JKLMN	01/02/2018	48	66	84	116
JKLMN	01/03/2018	48	63	132	179
JKLMN	01/04/2018	50	65	146	194
JKLMN	01/05/2018	64	80	162	208
JKLMN	01/06/2018	58	79	172	224
JKLMN	01/07/2018	53	75	175	234
JKLMN	01/08/2018	49	60	160	214
STUVW	01/01/2018	24	36	24	36
STUVW	01/02/2018	26	38	50	74
STUVW	01/03/2018	38	51	88	125
STUVW	01/04/2018	33	55	97	144
STUVW	01/05/2018	34	46	105	152
STUVW	01/06/2018	29	38	96	139
STUVW	01/07/2018	49	70	112	154
STUVW	01/08/2018	50	72	128	180

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.

[MarqueeCrew · Accepted solution]

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

Cheers!

Mark

[gflbrown]

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]

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

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]

@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

[gflbrown]

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]

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

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.

Week	Name	Location	source	Value	Moving_Sum_Value
202003	1001	ABC	SYSTEM1	26
202004	1001	ABC	SYSTEM1	27
202005	1001	ABC	SYSTEM1	28
202006	1001	ABC	SYSTEM1	29
202007	1001	ABC	SYSTEM1	30
202008	1001	ABC	SYSTEM1	31
202009	1001	ABC	SYSTEM1	32
202010	1001	ABC	SYSTEM1	33
202011	1001	ABC	SYSTEM1	34
202012	1001	ABC	SYSTEM1	35
202013	1001	ABC	SYSTEM1	36
202014	1001	ABC	SYSTEM1	37	378
202015	1001	ABC	SYSTEM1	38	390
202016	1001	ABC	SYSTEM1	39	402
202003	2001	PQR	SYSTEM2	26
202004	2001	PQR	SYSTEM2	27
202005	2001	PQR	SYSTEM2	28
202006	2001	PQR	SYSTEM2	29
202007	2001	PQR	SYSTEM2	30
202008	2001	PQR	SYSTEM2	31
202009	2001	PQR	SYSTEM2	32
202010	2001	PQR	SYSTEM2	33
202011	2001	PQR	SYSTEM2	34
202012	2001	PQR	SYSTEM2	35
202013	2001	PQR	SYSTEM2	36
202014	2001	PQR	SYSTEM2	37	378
202015	2001	PQR	SYSTEM2	38	390
202016	2001	PQR	SYSTEM2	39	402

Thanks in advance

[Mahadeva]

@gflbrown This macro doesn't have group by selection option. How were you able to achieve this.

[jrubin]

Is there a solution similar to this where the moving sum would "reset" for a new customer? For example, when your sample data switches from ID "ABCDE" to "JKLMN" in row 9, instead of the "RunTot_Measure 1" field returning 210, it would return 36 (just the first month of the data for the new ID.