Rolling 90 Day Calculation

[neilgallen]

(a precursory search didn't turn anything up, so hoping someone has an idea)

I need to do a rolling 90 day sum of sales on a dataset. There are a few conditions to this:

1) there is a field (model) I will be grouping by

2) if a particular model is not sold on a given day, there will be no record in the data.

Attached is the simplest solution I could think of that would cover all the options. I didn't want to write a multi-row formula with 90 rows as changing it later would be a pain and what's the fun in that, so this was my alternative. My fear is that this solution could get quite large, but after a date is 90 days past, my total does not change so after an initial run I can 'archive' a set of dates and append new dates as new data comes in.

I'm guessing there's a more direct way to accomplish this, but I'm unsure as to what. Any simpler ideas?

[MichalM]

Hi @neilgallen

Could you please help me understand how you define the 90 day rolling window? Is it 90 days back from the day the workflow is run?

Thanks

Michal

[neilgallen]

@Michal the 90 day window would be a rolling 90 day look at every date in this case between the beginning of the year and today.

Each date has a unique 90 day window of time consideration.

If date is today, then from 2018/06/14 until 2018/09/12.

If date is 2018/05/12 then from 2018/02/11 until 2018/05/12 .

Let me know if that makes sense!

[MichalM]

Ok, let's use the model 1 as an example, there are multiple dates. Do I need to establish 90 day rolling window for each of them? If so what is the desired output?

Model ID	Date	Quantity Sold
1	2018-01-09	1
1	2018-02-05	1
1	2018-08-17	1

[neilgallen]

Exactly. For each Model there would be a Rolling 90 day total for every day. In this case there were no sales for the other dates, so those dates would need to exist in the data as well (thus the multiple generate rows tools in my reference).

So:

Model ID	Date	Quantity Sold	Rolling 90
1	2018-01-09	1	1
1	2018-01-10	0	1
1	2018-01-11	0	1

and so on.

[clant]

Hi Neil,

I think you should use a macro for this.

The way id build it is id create a date-90 days in a new column, then within the macro id have a control parameter which uses that date to update a filter. After the filter you would summaries the value along with the Max date so you can rejoin this to your data.

I hope this helps, sorry I do not have time at the moment to write this one out.

cheers

chris

[MichalM · Accepted solution]

Hi @neilgallen

I tried couple of alternative approaches but the one you've used seems to be the most efficient. Batch macro increased the processing time from 12s to over 9min in this case.

M

[neilgallen]

Great. Thanks for the input! I typically assume that whatever i'm doing is NOT the most efficient method, but I guess you get lucky every now and then!

[benhowitt]

Hi Neil,

Looks like this is solved, but an alternative solution using rolling total can be found attached:

Or James Dunkerley's (slightly slower, but more flexible) version:
https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Rolling-Average-tool-enhance-running-sum-too...

All the best!

Ben

[oferk83]

Thanks for the solutions.

However, I find it hard to believe there is no slim solution b/c the above as far as I can tell (and have tried myself) create thousands of extra rows which slows down processing extra.

Is there no slim down macro/module with Alteryx to do these quite standard functions 'in the box'?