How do I graph 8 weeks of data automatically. Excluding previous weeks in a pivot.

All,

I have a pivot which has week ending totals from June 5th on back to January 9th. All I want the graph to do is to dynamically graph previous 8 weeks excluding the grand total in the pivot and 9 weeks back and before as well. For this example I want to graph data from June 5th back to April 17. Then next week I want to graph June 12 and back 8 weeks automatically.
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Sample data would be good.

I suspect easiest way would be to extract the relevant data from the whole set and then graph on the extract rather than the pivot.

Thanks
Rob H
0
Glenn RayExcel VBA DeveloperCommented:
You can use a dynamic range name to define the dates for your graph and force it to show only the most-recent eight weeks.

Do you have an example file available?  

-Glenn
0
Glenn RayExcel VBA DeveloperCommented:
Here is just one example of how you might do this.

The attached file has a sheet of transactions ("Data"), a PivotTable that summarizes them by weeks ("Pivot"), and a table and chart that only shows the last eight weeks of data ("Chart - Last 8 Weeks").

There is a named range called "OldestWeek" that calculates the week ending date that is seven weeks earlier than the most-recent date shown in the PivotTable.  Cell A2 of the Chart worksheet references that date and the cells below increment until the most-recent date.  GETPIVOTDATA functions return the sales amount.  These are then used to plot the data for the final eight weeks.

Note that I've also included another column on the Chart sheet (column C) that returns the total sales directly from the Data without the need to reference the PivotTable.  Similarly, the range "OldestWeek" could point to the Data and derive that value also.

Regards,
-Glenn
EE-RecentWeekCharting.xlsx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Edward PamiasTeam Lead RRS DeskAuthor Commented:
Ok, I am looking at this sheet and I think this will work if I can get the dates across the top going horizontal. I have Techs names going down the left and the dates going across the top and of course the data in the pivot.
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
I also need to exclude weekends and holiday from the formula.
0
Glenn RayExcel VBA DeveloperCommented:
You'll have to post a more-specific example.  I had to create mine from scratch and it's just a hypothetical demonstration of how one might proceed.  

Weekends and holidays can be addressed using various Excel functions, for example, NETWORKDAYS.

-Glenn
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
All,

Here is a sample sheet. What I want to do is graph automatically the last 8 weeks on the pivot. Then each week the pivot grows I need to graph the previous 8 weeks again.
samplesheet61015.xlsx
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
OK I figured out a way to get the dates to change each week. I just dropped =today() in a cell and I am using my weekending formula to display the weekending date for the week (the formula looks at the cell which has =today()  ) I deducted -7 -14 -21 etc up to 8 weeks - now all I need to do is figure out a formula that will look up the names in Column A  A3 - A38  and the dates in columns B2, C2, D2, E2, F2, G2, H2, J2 and display that data.
0
Rob HensonFinance AnalystCommented:
As per suggestion on follow on question:

On sheet1 add this formula to column K with header "Valid":

=IF(J2<TODAY()-(8*7),"","Valid")

Amend the pivot to include this data column and when refreshed drag the Valid field as a Report Filter. In the Pivot you with then get the Valid header and dropdown in B2 on Pivot sheet. Use the dropdown to select "Valid" and only 8 weeks of data will show in the pivot.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Thank you! This is perfect!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.