Rolling Filter for Table

[krisso]

I have a table that is filtered by monday dates to pick up the current monday date and the previous 10 mondays to create a rolling summary. I am wondering is there any way I can setup a dynamic filter so it always picks up the previous 10 weeks each monday rather than manually removing one date from the filter and adding a date to the filter each monday?

Thanks
Chris

 

[Gerald Higgins]

Do you actually want to filter OUT other week days Tue-Sun, and ONLY show data for Mondays ?

I'm guessing NO, I'm guessing you really only want data for the last 10 weeks, regardless of what weekday it is.
In which case in your filter you can use something like
Is after or equal to
TODAY()-70

That might sometimes lead to you picking up the wrong Monday, if that's a problem, you can adapt to something like
TODAY()-WEEKDAY(TODAY(),3)-70
Play around with the 3 value in the WEEKDAY function.

 

[krisso]

Sorry Gerald,

I didn't make that clear in my post. My table only shows Monday dates. It bascially picks up a file count for the week from the start of each monday. Currently each monday I manually filter out the date that is 12 weeks old and pick up the new week date.

I have tried both of your suggestions and get a blank return

If it helps this is the formula used in the table =COUNTIFS(Matter[[#All],[Fee Earner]],'New Matter Opened'!B$2,Matter[[#All],[matOpenDate]],">="&'New Matter Opened'!$A67,Matter[[#All],[matOpenDate]],"<"&'New Matter Opened'!$A68)

 

[Gerald Higgins]

OK so it was 10 weeks in the OP, now it's 12 weeks

I'm assuming you're using eitherExcel's Data, Filter, function OR an Excel table - either way the filter functionality seems to be basically the same.
And I'm assuming the column you are filtering on actually has an Excel data in it - is this correct ?

Can you confirm the above please, and if YES, we should be able to get a version of my formula to work, I'll post back on that.

 

[krisso]

I think my original post was correct, that it picks up the currrent weeks totals plus the previous 10 weeks what I said is each monday I manually remove the 12th week when bringing in the new week

The table dates look like this

11 wk rolling totals
Date
24/06/2019
01/07/2019
08/07/2019
15/07/2019
22/07/2019
29/07/2019
05/08/2019
12/08/2019
19/08/2019
26/08/2019
02/09/2019

I am indeed using the filter with in the table to pick up these dates and you are correct the column does have data in it

Thank you for your help

 

[Gerald Higgins]

Hmm . . . well this is embarassing . . . I was convinced that you'd be able to apply a formula to the filter criteria, to select a certain number of weeks back, but I can't actually seem to get that to work.

Another option would be to run a short macro which could easily do this - is that an option for you ?

 

[krisso]

I was convinced that a fomula in the filter would do the trick but failed spectacularlyin my efforts. I was hopeful that your formula was going to work as I had put = before my formula's

Was hoping to do it by formula rather than Macro as the person that uses the spreadsheet is a bit of a technophobe, and when I am not in the office I was hoping for something just to update automatically

 

[Gerald Higgins]

Yeah sorry about that - maybe there's a trick to getting Data Filter to work that way, but I don't know what it is.

If you decide to go down the code route, this might get you started - works for me on a basic level . . .

Dim Startdate As Single
Startdate = Date
Startdate = Startdate - 70

    Range("Table1[#Headers]").Select
    Selection.AutoFilter
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
        " >= " & Startdate, Operator:=xlAnd

I'm not a code expert, there may be better ways of doing this.
The number 70 is obviously meant to calculate the date 10 weeks before today's date.
If this should be 12 weeks, use 84 and so on.

 

[krisso]

Thank you for your help Gerald.