Possible 'calculated field' to total selected pivot table data ite

[Sarah (OGI)]

I have a pivot table whereby in column B I have a list of dates denoting when
the customer joined and in columns C-I I am showing the total business by
month, i.e:

Customer Ref Live Date Sept-07 Oct-07 Nov-07 (thru to Mar-08)
B01234 01/10/2007 £6834 £5702 £12614 . . .
B01111 01/09/2007 £4132 £7968 £18098 . . .

I'd like to show a running total amount of business placed by each customer,
but I'd like the total to refer only to those dates >= the given live date,
and up to 12 months after the given live date.
Therefore for customer B01234, I only need to total the figures for Oct-07
and Nov-07, whereas for customer B01111, I need to total the figures for
Sept-07, Oct-07 and Nov-07.

I was thinking that perhaps this might be achieved by the 'calculated
field/item' pivot table function?

Any help would be gratefully appreciated.

Thanks

 

[Debra Dalgleish]

In your source data, add a column, and compare the transaction date to
the live date. If it falls in the desired range, show the transaction
amount, otherwise calculate as zero.
Then, add this field to the pivot table's data area, as the "live" total.

 

[Sarah (OGI)]

Thank you - who knew it would be that simple? (I'm obviously just trying to
over-complicate things?!).

Thanks again!!