Rolling 12 months in Pivot Table automated

[jmartell]

Hi There,

I have a pivot table and each month I add the new month. I want to drop the last month so I maintain a rolling 12 weeks. I do not want to have to go into the pivot and relink my chart data. Is there a way to add a field into my data that would automate and refresh just the current rolling months?

Thanks,
J9

 

[brucesw]

How about a formula in your data that gives a "Show" or "Hide" result depending on the dates?

Such as; =if(A5>toady()-84,"Show","Hide")

Then build the pivot with the Show/Hide data in a Page setting but only "Show" displayed.

As time moves etc the "Show" data will roll over as you want. If your data is simpler (ie no formulas) a macro could insert the formula for you.

Good luck,

Bruce

 

[PaddyD]

this can be done without the addition of an extra column. the technique uses a dynamic named range - search the board for a variety of ways of setting one up. post back with info on where your data is, what you need selected etc if you can't get iot working.

 

[jmartell]

The Show Hide method may work although I can not get it to work. For example 38047 may be March 01 and 38670 may be Feb 03. I need help with a formula that will extract current month and year, otherwise it gives me Sept 03 as my current month when Aug 04 is actually my current date for my fiscal data.

Thanks for any help.

I did try the dynamic range but don't understand it.

J9

 

[PaddyD]

"post back with info on where your data is, "

 

[jmartell]

My data is in Column B and always adds each month by Row only. The field is called Time and it comes from a CVS file and the Excel number format is i.e 38706 = Feb 03.

J9

 

[jmartell]

PS. My total data sheet is in A1 to L47080. I have several charts that need to be linked to the pivot but need the pivot to roll only the last 13 current months. Since the Excel number format in the date is Year, Month and Day. I can not figure out how to write a formula to do this. I tried your dynamic range idea and got as far as =offset($A$1,0,0,Counta($A:$A)),Counta($1:$1) but this gives me the whole ball of wax!

Thanks again.

 

[PaddyD]

say I had the followeing:

Book1
	B	C	D	E
1	Date	Value
2	1/01/2001	1
3	1/02/2001	2
4	1/03/2001	3
5	1/04/2001	4
6	1/05/2001	5
7	1/06/2001	6
8	1/07/2001	7
9	1/08/2001	8
10	1/09/2001	9
11	1/10/2001	10
12	1/11/2001	11
13	1/12/2001	12
14	1/01/2002	13
15	1/02/2002	14
16	1/03/2002	15
Sheet1

...& wanted to set a range to cover only the last 12 entries. Defined ranges are st up using einsert | name | define. Define a name pivot_range to equal:

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-12,0,12,2)

Bit-by-bit:

=OFFSET(Sheet1!$B$1

- so we're starting from B1

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)

- & we're going down as many many rows as are counted to have something in them - which should be the end of the list if there's no gaps in the data

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-12

- then taking 12 off to move back up the list 12 rows to get the start point

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-12,0,

- not offsetting any columns

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-12,0,12,

- and the range we want is 12 rows "high" from the start point established above

=OFFSET(Sheet1!$B$1,COUNTA(Sheet1!$B:$B)-12,0,12,2)

- & my example has 2 columns, so we want the range 2 columns wide.

Set up a similar example yourself, to get the hang of it. check the range defined by hitting ctrl + G & going to pivot_range. Add some data to the bottom of the list & do it again.

To use this in a pivot, just set the pivot's target range as pivot_range in step 2 of the pivot wizard.

 

[jmartell]

Thank you, this is extremely helpful and I am starting to understand. Although I have two hurdles. 1) my data is not sorted on date when I get it and 2) I have several entries for each month. So my Time column looks something like this although much larger. So I need a formula that will look through everything and say: August 04 is most current...in the pivot table give me most current month by year and 12 months previous!

TIMEPER
Mar-04
Mar-04
Oct-02
Oct-02
Oct-02
Dec-01
Dec-01
Dec-01
Aug-01
Aug-01
Jul-01
Jul-01
Jun-01
Jun-01
Aug-03
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-04
Aug-03
Aug-03

 

[PaddyD]

can you sort your data?