Decreasing running total grouped by dates in Pivot. Excel 2007

[donchack]

Hi,

This is my first post here, I hope someone can help me with this as I have been unable to get a solution using running totals, calculated fields and differences from in the pivot table. I am quite sure the solution is not so difficult so hopefully someone can give me a hand on how to do it.

I have a set of data entries which are identified by an ID column. Some of this data has a planned expiry date, others have not. To be able to group by year, I have used a dummy date in year 3300 for non-expiring items.

I want to have a view of the remaining IDs by year. That means to be able to count the total number of unique IDs available at the begginning, then substract from the counts the ones that have an expiry date which is "older" than the year being counted. Eventually, this is just the opposite of what I have been able to get.

I would like to do this with a pivot table as the data may be changing quite frequently, and I would like to have a chart for graphical representation of this "decreasing" total.

Here is an example.

Data

AREA</SPAN>	MAIN ID</SPAN>	SECOND ID</SPAN>	EXPIRES</SPAN>	Expiry Date</SPAN>	Unique Main ID</SPAN>
AREA1</SPAN>	ID1</SPAN>	S_ID_1</SPAN>	Yes</SPAN>	31/12/3300</SPAN>	1</SPAN>
AREA1</SPAN>	ID1</SPAN>	S_ID_2</SPAN>	Yes</SPAN>	31/12/3300</SPAN>	0</SPAN>
AREA1</SPAN>	ID1</SPAN>	S_ID_3</SPAN>	Yes</SPAN>	31/12/3300</SPAN>	0</SPAN>
AREA2</SPAN>	ID2</SPAN>	S_ID_4</SPAN>	Yes</SPAN>	30/09/2015</SPAN>	1</SPAN>
AREA2</SPAN>	ID2</SPAN>	S_ID_5</SPAN>	Yes</SPAN>	30/09/2015</SPAN>	0</SPAN>
AREA3</SPAN>	ID3</SPAN>	S_ID_6</SPAN>	No</SPAN>	31/12/3300</SPAN>	1</SPAN>
AREA1</SPAN>	ID4</SPAN>	S_ID_7</SPAN>	No</SPAN>	31/12/3300</SPAN>	1</SPAN>
AREA1</SPAN>	ID4</SPAN>	S_ID_8</SPAN>	No</SPAN>	31/12/3300</SPAN>	0</SPAN>
AREA3</SPAN>	ID5</SPAN>	S_ID_9</SPAN>	Yes</SPAN>	01/01/2016</SPAN>	1</SPAN>
AREA1</SPAN>	ID6</SPAN>	S_ID_10</SPAN>	Yes</SPAN>	01/07/2015</SPAN>	1</SPAN>
AREA2</SPAN>	ID7</SPAN>	S_ID_11</SPAN>	No</SPAN>	31/12/3300</SPAN>	1</SPAN>
AREA2</SPAN>	ID7</SPAN>	S_ID_12</SPAN>	No</SPAN>	31/12/3300</SPAN>	0</SPAN>

<TBODY>
</TBODY>

A simple pivot grouped by year

Sum of Unique Main ID	Column Labels
Row Labels	2015	2016	3300	Grand Total
AREA1
ID1</SPAN>			1</SPAN>	1</SPAN>
ID4</SPAN>			1</SPAN>	1</SPAN>
ID6</SPAN>	1</SPAN>			1</SPAN>
AREA2
ID2</SPAN>	1</SPAN>			1</SPAN>
ID7</SPAN>			1</SPAN>	1</SPAN>
AREA3
ID3</SPAN>			1</SPAN>	1</SPAN>
ID5</SPAN>		1</SPAN>		1</SPAN>
Grand Total	2	1	4	7

<TBODY>
</TBODY>

Something similar to what I would like to have

Sum of Unique Main ID</SPAN>	Column Labels</SPAN>
Row Labels</SPAN>	2015</SPAN>	2016</SPAN>	3300</SPAN>	Initial Total</SPAN>
AREA1</SPAN>
ID1</SPAN>	1</SPAN>	1</SPAN>	0</SPAN>	1</SPAN>
ID4</SPAN>	1</SPAN>	1</SPAN>	0</SPAN>	1</SPAN>
ID6</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>
AREA2</SPAN>
ID2</SPAN>	0</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>
ID7</SPAN>	1</SPAN>	1</SPAN>	0</SPAN>	1</SPAN>
AREA3</SPAN>
ID3</SPAN>	1</SPAN>	1</SPAN>	0</SPAN>	1</SPAN>
ID5</SPAN>	1</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>
Grand Total</SPAN>	5</SPAN>	4</SPAN>	0</SPAN>	7</SPAN>

<TBODY>
</TBODY>

I am using Excel 2007 for Windows.

Thank you !!

Carlos