Decreasing running total grouped by dates in Pivot. Excel 2007

D

donchack

New Member
Joined
Mar 27, 2014
Messages
1
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

[TABLE="width: 556"]
<TBODY>[TR]
[TD]AREA</SPAN>
[/TD]
[TD]MAIN ID</SPAN>
[/TD]
[TD]SECOND ID</SPAN>
[/TD]
[TD]EXPIRES</SPAN>
[/TD]
[TD]Expiry Date</SPAN>
[/TD]
[TD]Unique Main ID</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA1</SPAN>
[/TD]
[TD]ID1</SPAN>
[/TD]
[TD]S_ID_1</SPAN>
[/TD]
[TD]Yes</SPAN>
[/TD]
[TD="align: right"]31/12/3300</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA1</SPAN>
[/TD]
[TD]ID1</SPAN>
[/TD]
[TD]S_ID_2</SPAN>
[/TD]
[TD]Yes</SPAN>
[/TD]
[TD="align: right"]31/12/3300</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA1</SPAN>
[/TD]
[TD]ID1</SPAN>
[/TD]
[TD]S_ID_3</SPAN>
[/TD]
[TD]Yes</SPAN>
[/TD]
[TD="align: right"]31/12/3300</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA2</SPAN>
[/TD]
[TD]ID2</SPAN>
[/TD]
[TD]S_ID_4</SPAN>
[/TD]
[TD]Yes</SPAN>
[/TD]
[TD="align: right"]30/09/2015</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA2</SPAN>
[/TD]
[TD]ID2</SPAN>
[/TD]
[TD]S_ID_5</SPAN>
[/TD]
[TD]Yes</SPAN>
[/TD]
[TD="align: right"]30/09/2015</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA3</SPAN>
[/TD]
[TD]ID3</SPAN>
[/TD]
[TD]S_ID_6</SPAN>
[/TD]
[TD]No</SPAN>
[/TD]
[TD="align: right"]31/12/3300</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA1</SPAN>
[/TD]
[TD]ID4</SPAN>
[/TD]
[TD]S_ID_7</SPAN>
[/TD]
[TD]No</SPAN>
[/TD]
[TD="align: right"]31/12/3300</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA1</SPAN>
[/TD]
[TD]ID4</SPAN>
[/TD]
[TD]S_ID_8</SPAN>
[/TD]
[TD]No</SPAN>
[/TD]
[TD="align: right"]31/12/3300</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA3</SPAN>
[/TD]
[TD]ID5</SPAN>
[/TD]
[TD]S_ID_9</SPAN>
[/TD]
[TD]Yes</SPAN>
[/TD]
[TD="align: right"]01/01/2016</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA1</SPAN>
[/TD]
[TD]ID6</SPAN>
[/TD]
[TD]S_ID_10</SPAN>
[/TD]
[TD]Yes</SPAN>
[/TD]
[TD="align: right"]01/07/2015</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA2</SPAN>
[/TD]
[TD]ID7</SPAN>
[/TD]
[TD]S_ID_11</SPAN>
[/TD]
[TD]No</SPAN>
[/TD]
[TD="align: right"]31/12/3300</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA2</SPAN>
[/TD]
[TD]ID7</SPAN>
[/TD]
[TD]S_ID_12</SPAN>
[/TD]
[TD]No</SPAN>
[/TD]
[TD="align: right"]31/12/3300</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


A simple pivot grouped by year

[TABLE="width: 368"]
<TBODY>[TR]
[TD]Sum of Unique Main ID
[/TD]
[TD="colspan: 2"]Column Labels
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels
[/TD]
[TD]2015
[/TD]
[TD]2016
[/TD]
[TD]3300
[/TD]
[TD]Grand Total
[/TD]
[/TR]
[TR]
[TD]AREA1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID4</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID6</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID2</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID7</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID5</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]7
[/TD]
[/TR]
</TBODY>[/TABLE]


Something similar to what I would like to have

[TABLE="width: 384"]
<TBODY>[TR]
[TD]Sum of Unique Main ID</SPAN>
[/TD]
[TD="colspan: 2"]Column Labels</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Row Labels</SPAN>
[/TD]
[TD]2015</SPAN>
[/TD]
[TD]2016</SPAN>
[/TD]
[TD]3300</SPAN>
[/TD]
[TD]Initial Total</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA1</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID4</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID6</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA2</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID2</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID7</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]AREA3</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ID3</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]ID5</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[/TR]
[TR]
[TD]Grand Total</SPAN>
[/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD="align: right"]4</SPAN>
[/TD]
[TD="align: right"]0</SPAN>
[/TD]
[TD="align: right"]7</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]


I am using Excel 2007 for Windows.

Thank you !!

Carlos
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You must log in or register to reply here.

Similar threads

J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
310
JMWh1t3
J
Sean15
  • Question Question
How to make pivot table show totals for specific date range
Replies
2
Views
982
Sean15
Sean15
D
  • Question Question
Reporting in a Pivot Table with a field spread across multiple columns
Replies
0
Views
313
dylan_work
D
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
338
Asbestos_Jen
Asbestos_Jen
D
  • Question Question
Having trouble getting Pivot Table data to pull from pivot table to regular table using =GetPivotData()
Replies
4
Views
996
smozgur
smozgur

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top