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 2ABCDE01/01/20188496ABCDE01/02/201884103ABCDE01/03/20187889ABCDE01/04/20187593ABCDE01/05/201887101ABCDE01/06/20186889ABCDE01/07/201884103ABCDE01/08/201890102JKLMN01/01/20183650JKLMN01/02/20184866JKLMN01/03/20184863JKLMN01/04/20185065JKLMN01/05/20186480JKLMN01/06/20185879JKLMN01/07/20185375JKLMN01/08/20184960STUVW01/01/20182436STUVW01/02/20182638STUVW01/03/20183851STUVW01/04/20183355STUVW01/05/20183446STUVW01/06/20182938STUVW01/07/20184970STUVW01/08/20185072 DESIRED OUTPUT: IDMonthMeasure 1Measure 2Measure 1 Roll3MonthsMeasure 2 Roll3MonthsABCDE01/01/201884968496ABCDE01/02/201891103175199ABCDE01/03/20187889253288ABCDE01/04/20187593244285ABCDE01/05/201887101240283ABCDE01/06/20186889230283ABCDE01/07/201884103239293ABCDE01/08/201890102242294JKLMN01/01/201836503650JKLMN01/02/2018486684116JKLMN01/03/20184863132179JKLMN01/04/20185065146194JKLMN01/05/20186480162208JKLMN01/06/20185879172224JKLMN01/07/20185375175234JKLMN01/08/20184960160214STUVW01/01/201824362436STUVW01/02/201826385074STUVW01/03/2018385188125STUVW01/04/2018335597144STUVW01/05/20183446105152STUVW01/06/2018293896139STUVW01/07/20184970112154STUVW01/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.

Create a moving sum/total in Alteryx Designer

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.

Workflow

Date Time

Accepted answers

MarqueeCrew

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

Cheers!

Mark

All comments

MarqueeCrew

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.

moving sum.yxmd

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!

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335