Pivot tables!
Posted by Richard on July 11, 2001 3:59 AM
I have an ever growing database that supplies a pivot table, i am having difficulties with summing periods
i have my columns split into years across the top and months down the side, which is fine if i just want to total calender years, or parts thereof.
problems arise if i want to total cross years for example a financial year April to March.
Thanks for any help received
Richard
Posted by Mark W. on July 11, 2001 6:15 AM
Richard, add a column 'Year' column to your PivotTable's
data list. To populate this column for years 1999 thru
2002 you can use the following formula...
=VLOOKUP(A2,{"4/1/1999",1999;"4/1/2000",2000;"4/1/2001",2001;"4/1/2002",2002;"4/1/2003",#N/A}+0,2)
Finally, within your PivotTable Group your 'Date'
field based only on Month and use the new 'Year'
field in the COLUMN area of your PivotTable.
Please note that the last entry in the lookup table
that serves as the 2nd argument of the VLOOKUP()
function will return #N/A. I recommend that this
always be the case regardless of how many years
you decide to translate using a function like this.
If #N/A appears as a 'Year' item in your PivotTable
that's your clue that you need to revise your lookup
table to accommodate additional dates. Of course,
your lookup table can be setup on the worksheet itself,
and referenced by the VLOOKUP() function.
Posted by Richard on July 11, 2001 8:34 AM
Neater but i dont fully understand
Thanks very much for the advice, this definately is a much neater way of producing analysis by years. Im still not sure it solves my problem.
1999 2000 2001
Jan 111 475 258
Feb 159 116 287
Mar 134 1,048 40
Apr 258 227 6,650
May 287 94 116
Jun 40 185 1,048
Jul 6,650 555 227
Aug 145 800 94
Sep 103 86 555
Oct 122 158 800
Nov 337 50 86
Dec 1,176 116 158
Which is fine if i want totals for a calender year
but how can i instantly get totals for say April 2000 to MArch 2001, without pasting or getting my calc out!
Many thanks
Richard
Posted by Steve M on July 11, 2001 9:40 AM
Re: Neater but i dont fully understand
Try to insert a column with labeled Fiscal Year or some such. Set up a two column table in a new sheet where you define the Fiscal Year. For example:
4/1/1999 1999
4/1/2000 2000
etc.
The populate the column in your data with
VLOOKUP(the_date_you_don't_want,New_Table,2)
for all dates 4/1/99 -< 4/1/00 the result will be 1999. Refresh your pivot table and drag the "Fiscal Year" to the table. Thanks very much for the advice, this definately is a much neater way of producing analysis by years. Im still not sure it solves my problem. 1999 2000 2001
Posted by Mark W. on July 11, 2001 10:34 AM
Re: Neater but i dont fully understand
Richard, the VLOOKUP() function assigns the financial
year to calendar dates. The total you're seeking
*would* be the total for the 2000 column. Is that
not the case? If so, perhaps you could provide
some sample data so we can talk specifics. Is the
ordering of the months in the ROW area causing some
confusion? If it is, that can be resequenced. Thanks very much for the advice, this definately is a much neater way of producing analysis by years. Im still not sure it solves my problem. 1999 2000 2001
Posted by Mark W. on July 11, 2001 10:43 AM
Re: Neater but i dont fully understand
Same as...
=VLOOKUP(A2,{"4/1/1999",1999;"4/1/2000",2000;"4/1/2001",2001;"4/1/2002",2002;"4/1/2003",#N/A}+0,2)
Posted by Richard on July 12, 2001 1:53 AM
Re: Neater but i dont fully understand
I understand thanks, i have to create a new column and then populate this with data to give me the correct analysis, what i was trying to achieve was a way of selecting different groups of months so i could have running reports i.e. 12 months to 30th june 2001 then 8 months to july 2001, etc. Not being tied down to one particular fiscal or calendar year.
I appreciate that i will have to change my data in order to do this.
Posted by Mark W. on July 12, 2001 6:18 AM
Re: Neater but i dont fully understand
Richard, there's a couple of ways you can handle this...
You can Group months such that Apr-Mar is Q1, etc.,
or you could hide (see the Pivot Field dialog) COLUMN
area items so that only the months of interest are
displayed. I understand thanks, i have to create a new column and then populate this with data to give me the correct analysis, what i was trying to achieve was a way of selecting different groups of months so i could have running reports i.e. 12 months to 30th june 2001 then 8 months to july 2001, etc. Not being tied down to one particular fiscal or calendar year. I appreciate that i will have to change my data in order to do this.
