Problem creating accurate Monthly/Quarterly/Yearly report.

**droopy777** · 12-15-2014, 05:31 PM

I have a list of over 200 companies that we buy from. My job is to track how many line items we purchase from each company and how many returns (if any) we have from those companies each month. Then need the percentage for that total.
For Example: We buy 45 items from ABC Company in June, and we have 2 returns to them. I need this to show the result of 95.556% rating for the month out of 100%
I also need to track the same for each quarter and yearly totals on an ongoing basis for each month in a percentage.
What I am looking for is a way to list all 200+ companies on one tab, and then if we only buy from about 25 of those companies each month, for only the companies we buy from each month to show up in the monthly report.

**ELeGault** · 12-16-2014, 01:12 AM

Consider a Pivot table. This will take little adjustment on your end and then you can quickly build out a view.

What you need to add to your current data on your Example of Current Way is a Date field. Date in which these line items pertain to.

From that you can insert a pivot (Make it out of your data) and then put the Sum of Line items and Sum of Defects in the values field, the Date followed by vendors in the Rows.

Values should take up columns

Now select the date field again which should be the first column of data, and go to Design. Select Group Field. Click/select all relevant, in your case 'Days','Months','Quarters','Years'

Let the pivot table worry about the math and grouping after that. It will only display relevant vendors in each section based on the dates you have entered.

Just remember to adjust the pivot table size as you enter new data and then to refresh the data. Really that is your best solution as it is quick, efficient and quite powerful.

**droopy777** · 12-16-2014, 03:45 PM

I appreciate the help, thank you. It is making me think of different ways to present this information. I am really new to this and am trying to figure out the Pivot Table. Adding a date would be just the end of the month date. I get a report each month and then have to manually input the line item total for each company and defects if any.

**ELeGault** · 12-16-2014, 07:05 PM

That will still work, though I would not select Days then so that it is not displaying just a single date, just choose the Month, Quarter and Year groupings. Hope that is the solution you need like I suspect it is. If this does take care of your current issue, then don't forget to mark this thread as Solved under thread tools above your initial post on the right.

Cheers