Show last 3 months on pivot chart

A

Andrew Ofthesong

Hi, i have a pivot table with monthly sales (year, month, amount)

i need a graph to show last 3 months

unfortunatelly, i can show "top 3 month by sale", but not last 3 months

actually, what i'm doing is add the "month" series as a data field, -appart
from a column name- and then filter "top 3 month by month", but this
generates me asmany extra columns as data are (example: a table with
incomes, outcomes, utilities, will have 3 columns with month)

how can i do this more easily and cleaner?
 
D

Debra Dalgleish

Assuming you have an actual date in each row of the data source, you
could do the following:

1. In a cell on the worksheet, calculate the first date that should be
shown in the PivotTable. For example, in cell J2:
=DATE(YEAR(TODAY()),MONTH(TODAY())-2,1)
2. Add a new column to the PivotTable, and compare the row date to the
date in cell J2. For example, if the dates are in column A:
=IF(A2>=$J$2,"Show","Hide")
3. Copy this formula down to all the rows of data
4. Refresh the PivotTable, to include the new column
5. Add the new field to the Page area, and choose "Show" from the
dropdown list.
 
D

Debra Dalgleish

Yes, step 2 should have said:

Add a new column to the PivotTable source data...
 

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

Top