PivotTable "show data value as" question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fred Chow - 03 Sep 2007 15:21 GMT

Hi all,

I have a problem about using Pivot Table in Excel 2003:

In a pivot table I have a date field, grouped by year and month, as the row
field. The data field of the table is anything that can be summed.

Now when I tried to display the data field value as "Difference from
previous month", I found that January of each year is always blank, ideally
the data value in January should be display as the difference between the
actual January value minus December value of the last year. Any tricks to
show them? Thanks a lot.

Frederick Chow
Hong Kong.

Reply to this Message

Debra Dalgleish - 03 Sep 2007 15:43 GMT

If you group the dates by year and month, the Difference From will start
over at the beginning of each year. There's no setting you can change to
make that behave differently.

You could add a field to the source data, and calculate the year and
month. For example, if the date is in column A:
=TEXT(A2,"yyyy-mm")

Add that field to the pivot table, instead of the grouped date, and the
Difference From calculation will flow from one year to the next.

> Hi all,
>
[quoted text clipped - 11 lines]
> Frederick Chow
> Hong Kong.

Signature

Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Reply to this Message

Fred Chow - 03 Sep 2007 16:57 GMT

What a shame that it is "built-in" bug...

Anyway thanks a lot.

Frederick Chow
Hong Kong.

> If you group the dates by year and month, the Difference From will start
> over at the beginning of each year. There's no setting you can change to
[quoted text clipped - 22 lines]
>> Frederick Chow
>> Hong Kong.

Reply to this Message

Rate this thread: