• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Get Last Week, etc. Date Filters in Report Filter

ann99m

New Member
How can I get the dates in the pivot table filter field to sort by "last week" "last month" "this year" "last quarter" etc? They show when I have a date format in the Axis Fields (categories) but when I want to use the date to just filter what time period is shown without actually showing dates on the chart I have to choose individual dates, one at a time. Please see the website below for some quick screenshots to see what I mean.


Thank you!


https://sites.google.com/site/pivotq/
 
I think you need a helper column for the pivot table. Set condiitons for a if then condition comparing the dates on each cell against today(). you may also want to use month() function in conjunction with today(), such as month(today()) to set condition for last week -7 to -14. you may want to think about a situation where a date can be both last week and last month.


if today is Dec 1, 2011 and you have a date that is Nov 29, 2011. it fulfill both last week and last month condition. so you need to decide which one you need to go with.
 
thanks for the idea Fred. I'll check into it, but hoping to avoid this. I already have several helper columns in the data and am hoping not to have to add too many more!
 
Back
Top