Have a set of data. 750k records. Large number of fields. Only really need 2 fields and create a new calculated one. I want to sum up those 750k to look like this: 1 record per dept, per month. I need to solve for Average of 12 prior months. ie if the month is July 1, 2017, I need average of July 1 2016 - June 30 2017. One row per month as laid out in the table. DeptMonthAverage $$ Prior 12 monthDept 1Jan 1 2017 Dept 2Jan 1 2017 Dept 3Jan 1 2017 Dept 4Jan 1 2017 Dept 5Jan 1 2017 Dept 1Feb 1 2017 Dept 2Feb 1 2017 Dept 3Feb 1 2017 Dept 4Feb 1 2017 Dept 5Feb 1 2017 Dept 1mm/dd/yyyy Dept 2mm/dd/yyyy Dept 3mm/dd/yyyy Dept 4mm/dd/yyyy Dept 5mm/dd/yyyy So in my data: I have a dept, a date, a paid column. I have a date column that is the first day of the month of the given date. Don't believe it should be that hard. Thanks in advance.

Prior 12 month average

Have a set of data. 750k records. Large number of fields. Only really need 2 fields and create a new calculated one.

I want to sum up those 750k to look like this: 1 record per dept, per month. I need to solve for Average of 12 prior months.

ie if the month is July 1, 2017, I need average of July 1 2016 - June 30 2017. One row per month as laid out in the table.

Dept	Month	Average $$ Prior 12 month
Dept 1	Jan 1 2017
Dept 2	Jan 1 2017
Dept 3	Jan 1 2017
Dept 4	Jan 1 2017
Dept 5	Jan 1 2017
Dept 1	Feb 1 2017
Dept 2	Feb 1 2017
Dept 3	Feb 1 2017
Dept 4	Feb 1 2017
Dept 5	Feb 1 2017
Dept 1	mm/dd/yyyy
Dept 2	mm/dd/yyyy
Dept 3	mm/dd/yyyy
Dept 4	mm/dd/yyyy
Dept 5	mm/dd/yyyy

So in my data: I have a dept, a date, a paid column.

I have a date column that is the first day of the month of the given date.

Don't believe it should be that hard. Thanks in advance.

Datasets

Transformation

Expression

Accepted answers

neilgallen

@M_ThompsonWatermark I would recommend checking out the "Moving Summarize" tool within the Crew Macro Pack. This will calculate moving averages (among other things) across any number of rows specified.

That's the easiest route, compared to setting up a multi-row formula tool that requires inputs for at least 12 rows. Also allows the greatest flexibility should your time window change in the future.

Good luck!

All comments

ddiesel

HI @M_ThompsonWatermark!

I think I understand from your post what you're trying to do. Here's how I might tackle it:

1) Summarize (Group by Dept, Group by Month, and Sum_Paid)

2) Sort by Date

3) Sample the last 12 records to isolate the previous 12 months

4) Summarize (Group by Department and Average_Sum_Paid)

Let us know if that gets you close enough. If not, please provide a sample input and desired output.

M_ThompsonWatermark

No, apparently I didn't explain it well.

750K records. Each is a record of something paid. Each dept paid several thousand paid records (invoices) a month. If you were looking at if for 'that' month, you'd say what was the average paid for that current month. But I don't want an average of just that month. for each month I want an average of all the paids for the 12 months prior. So it's a 'rolling' average. Each month is identifying the average of all the invoices paid the prior 12 months. (for that dept).

Does that help?

ChrisTX

Searching the forum for "rolling" yields a few interesting posts

neilgallen

That's the easiest route, compared to setting up a multi-row formula tool that requires inputs for at least 12 rows. Also allows the greatest flexibility should your time window change in the future.

Good luck!

ddiesel

@neilgallen Thanks for bringing up the Moving Summarize tool! I didn't even know that was in the Crew Macro Pack! Very helpful!

@M_ThompsonWatermark Thanks for your reply. I think I now understand what you're trying to do. There may be a less complicated way to solve this, but this is all I could think of based on your description.

Workflow:

Macro:

Output:

Please give this a try and let us know if it works for you!

Prior 12 month average R ddiesel.yxzp

M_ThompsonWatermark

While the crew macro didn't turn out to be the complete answer, it contained a key element in the approach I used for answering my dilemma. I used a couple of summary tools in a row along with the crew macro, and was able to get exactly what I needed. Thanks!!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703