How do I get running 12 months of data using date filter?

6

68jcode

I am using Excel 2007 connected to a SQL DB and use pivot tables for
reporting purposes. I have multiple years worth of data that I access, but
want to limit my pivot table to display just the last 12 months. I can't
seem to find a way to do this, but am sure someone must have solved this
problem already. I consider myself pretty Excel savvy, but I'm no programmer
so hold the VB please.

Thansk!
 
B

Barb Reinhardt

Why don't you add a helper column that determines if the data is within the
last year.

Do something like
if(A1 > date(year(Today())-1,month(today()),day(today())), "YES","NO")

hopefully I have all of the parens. PUt that helper column in as a page
field.
 
6

68jcode

I can't do a helper column because the data is in a SQL DB that I am
accessing, not just in another worksheet that I can manipulate. Any other
thoughts?
 
P

Paullyie

Hi,

Not sure if this will help, but worth a try.
How exactly are you accessing the information in the SQL DB, I'm assuming
you are using the Microsoft Query through ODBC etc...?

If so, you probably have a field available with a date of transaction,
restrict this field with a filter

In excel right click on the data you are accessing from SQL, and select
'Table' then 'edit query' this will bring up a query wizard.
CLick next and go to the 'Filter Data' screen, here you can select your date
field and apply the criteria to restrict it to the year you require.

Hopefully this will help, if not could you provide a bit more info on how
your data is extracted from SQL

Cheers
 
6

68jcode

Thanks for the response. This really doesn't help me as it's the same
concept as applying the filter on the "date created" field in my pivot table.
I'm not just trying to limit the date range to greater than, less than or
between etc., which are the typical filters. I need to just pull the last 12
months, which I'm assuming requires some logic or a calculation, which I am
not sure can be done in a pivot table. Any thiughts?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

pivot table rolling 12 month filter? 1
rolling 12 month date filter for pivot table? 3
How do I convert dates to months in Pivot Tables? 1
How do I calculate FMLA hours used in the last rolling 12 months 2
How I can set dynamic pivot table ONLY when I have enough data point 1
How do I save Pivot Table Data 4
find date last used, deativate if older than 12 months 6
Presenting all months in a year in a Pivot Table/Chart when allmonths are not included in source dat 0

Top