Hi,
I am trying to get my pivot table to average the last 5 weeks average to compare it to the current weeks sales.
Could this be done? The attachment is how the pivot table should be.
Regards,
Hi,
I am trying to get my pivot table to average the last 5 weeks average to compare it to the current weeks sales.
Could this be done? The attachment is how the pivot table should be.
Regards,
If you add a "Category" helper-column to your data, you can get the results you want.
Based on the date, the Category column formula would return:
Curr
AvgRng
Other
Then your pivot table can use that Category in the column section and average the values.
Since the Curr category would only have one date, the average would be the total.
The AvgRng would have 5 dates and calculate their average.
Hide the columns you don't need.
Is that something you can work with?
Hi Ron,
Thank you for your feedback, but the truth of the matter is that I have report on thousand of stores and products. My true average range will consist of a 25 period average. So to hide all the columns will be a time consuming exercise.
My Pivot Table is based on data in a Ms Access Table. I thought that it will be better to group everything in access with queries and just pull that information to excel for charting. But I am pinned there as well, as the sql query doesn't allow me to run aggregated data. Could be the users fault as my skill in Access is a little bit rusty.
Regards,
Willem
Hello Wilem87 and Welcome to Excel Forum.
There may be more to this than I am considering, however I have attempted to construct the a table raw data that might feed a pivot table such as the sample shown in the file. Note that the table was produced using Power Query which is a standard feature in the 2016 version of Excel. The dates in the resulting Pivot Table can be changed using Date filters (cell F2) > Between. I you choose the dates 2/10/2019 and 3/17/2019 you'll see that average of the previous five weeks updates. The formula used for previous five weeks is the same as in the sample.
Perhaps if you could upload a desensitized sample of the raw data or Access table we could be of more help.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi JeteMc,
Thank you for your feedback and solution.
You're Welcome. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks