Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

Need to calculate a rolling 4 week average in pivot table
Solved by X. B. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 01/08/2018 - 07:50
hello,
User 01/08/2018 - 07:50
I need to calculate a rolling 4 week average in a pivot table
Excelchat Expert 01/08/2018 - 07:50
Hello!
Excelchat Expert 01/08/2018 - 07:50
Welcome to got it pro!
User 01/08/2018 - 07:50
similar to the (% difference from previous week function)
User 01/08/2018 - 07:51
except it needs to be % difference from previous 4 week average
Excelchat Expert 01/08/2018 - 07:51
Sure.
User 01/08/2018 - 07:51
it needs to be within the pivot table not calculated off of the pivot table
Excelchat Expert 01/08/2018 - 07:51
Could you show me some sample data?
User 01/08/2018 - 07:51
so a calculated field of some sort within the pivot
Excelchat Expert 01/08/2018 - 07:51
Sure
Excelchat Expert 01/08/2018 - 07:52
Let's see how to do it
Excelchat Expert 01/08/2018 - 07:53
So you want the percentage of?
User 01/08/2018 - 07:53
% difference from previous 4 week rolling average
User 01/08/2018 - 07:54
It needs to either be an automatic calculation in the table or as a calculated field in the pivot
Excelchat Expert 01/08/2018 - 07:54
ok I see
User 01/08/2018 - 07:55
but this is a sample dataset
Excelchat Expert 01/08/2018 - 07:55
Sure
Excelchat Expert 01/08/2018 - 07:55
What is D5 should be in this case?
User 01/08/2018 - 07:55
my dataset is much larger and is connected by an access connection
Excelchat Expert 01/08/2018 - 07:55
Sure
Excelchat Expert 01/08/2018 - 07:56
what is the value in D5?
User 01/08/2018 - 07:57
It needs to be done through a calculated field or some type of access query
Excelchat Expert 01/08/2018 - 07:57
Ok
User 01/08/2018 - 07:57
ive been trying to figure this out for quite some time and have narrowed it down to that
Excelchat Expert 01/08/2018 - 07:57
So far, I think the best way to do this is go through this way
Excelchat Expert 01/08/2018 - 07:58
And then create another column for percentage calcualtion
User 01/08/2018 - 07:58
no
User 01/08/2018 - 07:58
i am running a pivot based off a table connection
User 01/08/2018 - 07:58
I dont have an actual table to manipulate
User 01/08/2018 - 07:59
unless its manipulating the table within access
Excelchat Expert 01/08/2018 - 07:59
Yes
Excelchat Expert 01/08/2018 - 07:59
I think that the easiest way to do so
User 01/08/2018 - 07:59
so this will be done in access?
Excelchat Expert 01/08/2018 - 08:00
Yes. it will be easier
User 01/08/2018 - 08:00
ok how
Excelchat Expert 01/08/2018 - 08:00
I have tried to use pivot table
Excelchat Expert 01/08/2018 - 08:00
But even in pivot table, it is still the save to manully inseert another column for rolling average and percentage calculation
Excelchat Expert 01/08/2018 - 08:01
So, eseentially that will be the same
User 01/08/2018 - 08:01
so how do I add the rolling average in access
Excelchat Expert 01/08/2018 - 08:02
Wait. In excel, there might be a way to do this
Excelchat Expert 01/08/2018 - 08:02
To calculate a moving average, first click the Data tab’s Data Analysis command button.
Excelchat Expert 01/08/2018 - 08:02
When Excel displays the Data Analysis dialog box, select the Moving Average item from the list and then click OK.
Excelchat Expert 01/08/2018 - 08:03
find the data that you want to use to calculate the moving average.
Excelchat Expert 01/08/2018 - 08:03
In the Interval text box, tell Excel how many values to include in the moving average calculation.
Excelchat Expert 01/08/2018 - 08:03
Then tell Excel where to place the moving average data.
Excelchat Expert 01/08/2018 - 08:03
After you finish specifying what moving average information you want calculated and where you want it placed, click OK.
User 01/08/2018 - 08:06
i cant find a data analysis button
User 01/08/2018 - 08:06
nvm
Excelchat Expert 01/08/2018 - 08:07
Can you find from your local MS excel?
User 01/08/2018 - 08:07
it doesnt allow for a dynamic average
Excelchat Expert 01/08/2018 - 08:07
is there any error msg?
User 01/08/2018 - 08:07
if I add another week to my pivot i will have to recalculate
Excelchat Expert 01/08/2018 - 08:08
can you include a larger range?
User 01/08/2018 - 08:08
no
User 01/08/2018 - 08:09
i need for it to be built in to either the pivot as a calculated field or as an access query\
User 01/08/2018 - 08:09
the solution you provided is a manual fix not an automatic calculation
Excelchat Expert 01/08/2018 - 08:10
Yes since from excel level this is the only way to do so
User 01/08/2018 - 08:10
what about access
Excelchat Expert 01/08/2018 - 08:10
If you need a automatic way to do this, you may need to consider access
Excelchat Expert 01/08/2018 - 08:10
Since this is a excel platform, we really not be able to assist with access portion
Excelchat Expert 01/08/2018 - 08:10
We do apologize for this inconvience

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc