Need a pivot table to show rolling history

thanks Mike for the suggestion, do you have a template for a dashboard? That actually would be awesome to have.

What am I doing wrong? the "attach file" isn't an option on the toolbar above

Hi Iduram,
ive attached a sample for you to have a look at. My personal preference is not to have dates across as it becomes messy. Keep it simple and use tables to maintain the data,along with drop down boxes etc then pivot that. I hope the sample points you in the right direction for future.click in a pivot to see where the filters are, and you should be able to work it out. Sorry but I completely killed your charts in the process

Wow, that is awesome ! Thank you Very Much.
Q1. Do I only update my "2 weeks" on the rolling data page then my charts will update once I refresh?
Q2. How Can I change the charts you created ( awesome btw) to show a "total" at the end of the chart (perferably a different series type) Line for the rolling and column for 'grand total'.
Thank you so much, LaDonna

Also, when I try to refresh the pivot tables I'm getting " reference not valid".

strange I don't get that. Ive deleted the chart tab. see if that works for you. is the format what you where looking for?

THANK YOU ! you have no idea how this will help me.

Wow, I'm in awe at the moment at all the work you did for me. I really appreciate it! And the largest to smallet chart, you read my mind! I love it, and so will my manager. But I'm still getting the Invalid reference when I try to refresh the tables.
Example: I'm sorting out the dates on the 2wk rolling sheet to show only July, then I click inside of the pivot tables and refresh. that is when I get the invalid reference. I tried the Ctrl Alt F5 too and nothing happens. What am I doing wrong?

This is odd, ive checked there are no data connections in the workbook, so lets start from the beginning. Maybe its a clash between old and new. at the beginning I killed all your charts. So attached is a fresh book. can you update the pivot data in this? and we will build it up.

Interested, I sent a reply to your message, Let me know if you get it. it isn't showing in my sent folder ?

need pivot example new(2).xlsx

I finally got the error to go away, Once I changed the date format from Azeri(Cyrillic) to English US the error was gone.
And when I added a couple new dates and refreshed they showed up. YEAH ! BUT - when I change the filter in the data to show only July, then go to the pivot table and refresh, it still shows all of Supplier 1. (july and aug)

Ok,at least you know why that part was not working. On the data entry part, some of the dates where still in UK format. Have a look at the attached to guide to the next part of the dashboard. This is where you build up your supplier pivots. ive made the dates US and added a few more. Have a go and see if you can build the pivots up as instructions.

Question - If I filter the data to show only July , then refresh the pivot table, shouldn't the table change to show only July? I don't understand why it isn't updating.

It is attached in this thread , can you see it?

the one from 8/19 is my original one.

Ok, so I want to
1. be able to update daily any defects that a supplier has for that day. ( I have 42 suppliers).
2. be able to show a "rolling two week" history in a chart for each supplier. ( I will need to keep all the data on the data page for at least one year)

Interested and I are almost there, but I keep getting *&^%$ reference not valid..

Thanks everybody , LaDonna

Hi idurham,are you filtering the table 2wk rolling data tab into months, then looking at the pivot data? expecting to see the same result as both work separately. Filtering the rolling data table does not affect the pivots. Question for you. can you work with week numbers to collect your data. ie we are now in week 35 and could group your data this way. ill attach another work book with some more details later

idurham, attached is the original workbook you mentioned in earlier post. it works for me, but you may have to do what you did to the last workbook to get it to refresh. Ive added some more things as I think you are mixing up both table and pivot. ive split it into weeks, so you can have an easier way to see a rolling 2 weeks whilst keeping a record for 12 months min. Ive done 10 supplier charts ( roughly formatted ) so you can see, and added some notes. If you can get it to refresh, update the format of the 10 charts to what you want, repost that workbook and we can continue.

FINALLY got it to work right. I love the Vlookup you added for the week, I will use this , not just here but other data I have to track so THANK YOU ! But now here is my problem, I have to update these charts everyday, so rolling 2 weeks for this situation is add one day to the end of chart's data, and drop one day at the beginning.. ( showing two weeks) changing everyday. I will be able to work with what you completed so far and create what I need. your better than awesome, so thank you for all the hard work you put into this.

If you go on date filter, then date filters. choose between, and you get a date picker. it just means each day you would have to do this for each supplier. I used the vlookup date previously to be able to split out my data into daily, weekly monthly and chart for each. It works for me so glad you may be able to use this for more. Any more help you need just ask.