Pivot graphs

Hi There,

I am really struggling with a dashboard that uses a number of pivot tables. the pivots run in the background on hidden tabs and the pivot graphs are all on the front page. The pivots update themselves automatically from our databases. The problem is that we report on data weekly, and whilst we are only in week 18 it looks ok, the graph will get messy when we reach week 30 and onwards.

There are 2 solutions i can think of but am unsure how to do either of them.

Solution 1

Make the pivot chart only show the current week plus the last 8 weeks

Solution 2

Make the pivot chart have a scroll bar that will enable the user to see only 8 weeks at a time but all the data is visible using the scroll bar.

I am unsure is this is to attempt this straight from the pivot table or whether I would need to copy the information to a new sheet. If this is the case, is there a formula that will enable the graph not plot the weeks there are no data for as 0.

Many thanks