Need advice for Pivot table

[Julie]

Good day everybody,

I am new to Pivot table and I need to build a database for a pivot table to
use.

The database will have the dimension of :
1. Company
2. Category
3. Account

The values are :
1. Last Year monthly amount (i.e. 12 figures)
2. Last Year-to-date amount(i.e. 12 accumulative figures)
3. This Year monthly amount (i.e. 12 figures)
4. This Year-to-date amount(i.e. 12 accumulative figures)
5. Next Year monthly amount (i.e. 12 figures)
6. Next Year-to-date amount(i.e. 12 accumulative figures)

I would like to seek your advise on how do I organise my data structure to
enable pivot table to work efficiently.

A. Do I make the values to be separate fields i.e. 6x12 = 72 fields?

B. Do I make them with three fields as below.
Year = 2003, 2004, 2005 (i.e. Last Year, This Year, Next Year)
Period = 1 to 12 (i.e. 1=Jan, 2= Feb.....)
Amount

Method A will have less records but more fields.
Method B will have more records but less fields.
Which method will give the best structure for Pivot table?

For Method B, if the records in my database(DBF format) has more than 65,536
records(exceed Excel maximum rows), will I have problem to link it to Pivot
table?

Thank you in advance and I hope to hear from you.

Best regards,
Julie

 

[Mike Lewis]

Julie,

I've linked pivot tables to external dbs with greater than
65k records, and that is not a problem.

The pivot tables will handle your YTD fields, so they do
not need to be included in the DB structure, or if they
are, they will not need to be included in the Excel Pivot
Table structure.

I think you can accomplish all you describe with a DB
structure of:

Year / Month / Company / Category / Account / Sales

Where "Sales" is the numeric value you want to track.

Then in your pivot table, you can set it up so that you
are comparing YTD between last year, this year and next
year. This may require a little "grouping" or hiding some
of the "future" months, but certainly fairly easy.

My suggestion is try it on a small sample of "bogus" data
and see how it works for you.

Good luck

Mike

 

[Julie]

Thank you very much, Mike.
Your explanation is very helpful. I shall take your advise.

Have a nice day.

Best regards,
Julie