Relative Date Filter to show last x dates pivot table

[Confidentjohn]

Hi

I have a pivot table report that uses a dynamic range as its source, the data is continually growing with new data sets added each day into the data table.

To update the pivot I have added a Macro button which refreshes the pivot table, however as new dates are added each day the Value section is just constantly growing.

The Pivot Table structure is as below

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Product Name[/TD]
[TD]Product Cat[/TD]
[TD]01/02/14[/TD]
[TD]02/02/14[/TD]
[TD]03/02/14[/TD]
[TD]04/02/14[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]a[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]b[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]c[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]d[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]e[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]












If I add data to the source for the 05/02/14 and refresh the table a new column will be added expanding the width of the table.

I'd like to add a filter that when the table refeshes only shows the last 3 days (for example)

Looking in the pivot report filters these are all very specific when it comes to dates, you need to know the exact range, however I want a relative filter last 3 days.

I am not adverse to the filter being applied with a bit of VBA, is there a way to find the last date and subtract 2 and these are the values to filter on?

Any help would be greatly appreciated.

Cheers

John

 

[Fazza]

hi, John

A filter for the last 3 days can be included in the pivot table definition when it is created, then each time a refresh is done only data for the last three days will be in the table. (There could be variations on this such as linked instead to a worksheet cell so that the 3 was variable.)

I'll assume the data is on worksheet named YourWorksheet and you have a date field named "Date". That field name might be a problem, but I'll assume not. There could be a little detail wrong, I haven't checked, if the time is after 12noon whether the day will be out by one. If so that can be readily fixed. I won't check it right now as just want to explain the solution.

Save the file. From a new file, ALT-D-P to start the pivot table wizard, choose external data source, Excel files, OK, browse for your file, OK, if you get a message about no visible tables OK to acknowledge then via 'options' select 'system tables' and see your worksheet names, choose YourWorksheet and a field from there, continue through the wizard to the last step & choose the option to edit in MS Query. Via the 'SQL' button see the current SQL, fully replace it by

SELECT *
FROM [YourWorksheet$]
WHERE CLng([Date]) > CLng(Now) - 3

OK to enter that, and if you get a message about 'not being able to graphically represent ..' just OK to it, see the filtered dataset, via the 'open door' icon exit MS Query & complete the pivot table. You can move the worksheet containing the pivot table into the source data file if you want. Untested.

regards

 

[Fazza]

Or you might prefer to simply add a new field to the source data, ShowIt, and populate it with a formula to return TRUE/FALSE or YES/NO based on the desired filtering by date. Make the ShowIt field a page field in the pivot table and set it to TRUE or YES or whatever works for you. regards