Pivot table update within groups

[mlmoyer]

I have a data table that looks somewhat like this a number of rows of "Date", "ID#" and other extraneous fields.

I have a pivot table that simply lists the total count by date, then i grouped it by month and then by quarter:

<table x:str="" style="border-collapse: collapse; width: 220pt;" border="0" cellpadding="0" cellspacing="0" width="293"><col style="width: 69pt;" width="92"> <col style="width: 43pt;" span="2" width="57"> <col style="width: 65pt;" width="87"> <tbody><tr style="height: 13.5pt;" height="18"> <td style="height: 13.5pt; width: 69pt;" width="92" height="18">
</td> <td style="width: 43pt;" width="57">
</td> <td style="width: 43pt;" width="57">
</td> <td style="width: 65pt;" width="87">
</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl22" style="height: 15.75pt;" height="21">Date4</td> <td class="xl23">Date2</td> <td class="xl23">Date</td> <td class="xl26">Count of ID</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17">2008-Q4</td> <td class="xl28"> </td> <td class="xl28"> </td> <td class="xl24" x:num="" align="right">76</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl33" colspan="2" style="">October 2008</td> <td class="xl32" x:num="" align="right">29</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39722">10/1/08</td> <td class="xl25" x:num="" align="right">8</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39736">10/15/08</td> <td class="xl25" x:num="" align="right">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39742">10/21/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39752">10/31/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl31">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl33" colspan="2" style="">November 2008</td> <td class="xl32" x:num="" align="right">25</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39753">11/1/08</td> <td class="xl25" x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39767">11/15/08</td> <td class="xl25" x:num="" align="right">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39773">11/21/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39781">11/29/08</td> <td class="xl25" x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39782">11/30/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl31">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl33" colspan="2" style="">December 2008</td> <td class="xl32" x:num="" align="right">22</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39783">12/1/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39797">12/15/08</td> <td class="xl25" x:num="" align="right">19</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39803">12/21/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl30" x:num="39813">12/31/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl29" style="height: 12.75pt;" height="17"> </td> <td class="xl31">
</td> <td class="xl31">
</td> <td>
</td> </tr> </tbody></table>

My problem is, when i add a new row to the main table, say with a date of "December 20, 2008", and refresh the pivot table, the information ends up clear at the bottom (in this case, after the year 2014).

What am I doing wrong? I figured the pivot refresh would place the data among the other December 2008 rows in the pivot table.

Thanks.

Mark

 

[nigelk]

Try clicking on the range and select "field settings" then "advanced" you can then sort ascending/
descending.

 

[mlmoyer]

thanks - I tried your suggestion but it didn't move; new entries still end up at the very bottom. Does grouping have anything to do with it?

 

[RoryA]

Your date may be interpreted as text - if you enter it as 12/20/2008 does it behave properly?

 

[mlmoyer]

I checked both the format of the maindata and the pivot table fields, and they're both date fields. For grins i tried resorting (thru Field Settings/Advanced) and it didn't move; refreshed; resorted; still at the bottom.

 

[RoryA]

if you change the format of the cell where you just entered the date, does the display change? If not , it's text and not a date.

 

[mlmoyer]

<table x:str="" style="border-collapse: collapse; width: 250pt;" border="0" cellpadding="0" cellspacing="0" width="333"><col style="width: 69pt;" width="92"> <col style="width: 73pt;" width="97"> <col style="width: 43pt;" width="57"> <col style="width: 65pt;" width="87"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl35" style="height: 12.75pt; width: 69pt;" x:num="39724" width="92" height="17">10/3/2008</td> <td class="xl36" style="width: 73pt;" width="97"> </td> <td class="xl26" style="width: 43pt;" width="57"> </td> <td class="xl24" style="width: 65pt;" x:num="" align="right" width="87">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17"> </td> <td class="xl37" x:num="39724">October 2008</td> <td class="xl34"> </td> <td class="xl33" x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl27" style="height: 12.75pt;" height="17"> </td> <td class="xl38">
</td> <td class="xl28" x:num="39724">10/3/08</td> <td class="xl25" x:num="" align="right">1</td> </tr> <tr style="height: 13.5pt;" height="18"> <td class="xl27" style="height: 13.5pt;" height="18"> </td> <td class="xl38">
</td> <td class="xl29">
</td> <td>
</td> </tr> <tr style="height: 16.5pt;" height="22"> <td class="xl31" style="height: 16.5pt;" height="22">Grand Total</td> <td class="xl39"> </td> <td class="xl30"> </td> <td class="xl32" x:num="">2014</td> </tr> </tbody></table>
All three pivot table columns are dates (i checked Format Cells). I changed the format of the cells in the physical data itself to "General" and all the dates changed to the typical number it should be, then i set them back to "Date" and they're back to mm/dd/yyyy.

 

[RoryA]

Ah - how did you do the grouping? Did you just right-click the date field, choose Group and show detail->group, and then choose year, quarter and month?

 

[mlmoyer]

I selected weeks to be a selection, right-clicked and "grouped" them into a month, then selected months and grouped them into quarters. ... i just selected the field - it's just a standard date field - or am I misunderstanding your question?

 

[RoryA]

If you grouped them all manually like that then you won't get automatic grouping. If you drag your date field to the row area, then right-click it and choose Group and show detail->Group, you should get options to group on Weeks, months, quarters and years. If you use that method, it should automatically group any additional data.