+ Reply to Thread
Results 1 to 9 of 9

Pivot Filter not allowing filter by year, month and date

  1. #1
    Registered User
    Join Date
    02-17-2017
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Pivot Filter not allowing filter by year, month and date

    Hi Guys,

    I am trying to build a cash forecasting model for payments to suppliers.

    I am basically extracting aged creditors with various currencies as my data and need to be able to quickly show values payable by currency as at certain dates. I have used pivot to display information as required and in the filter option on pivot wizard I have selected due date to be able to quickly filter between whatever dates are required (i.e. my management team ask for). I would then hope to filter on this to be able to quickly filter by year, months and days to show amount payable in each currency upto that due date (as picked in due date filter on pivot)

    My problem is the filter function is not displaying dates in the pivot as it does in the data sheet (see screenshots to try and explain)

    Data Filter

    Data Filter.PNG

    Pivot Filter
    Pivot Filter.PNG

    How can I get it so I can expand on year, months and days on pivot filter as it would on filter function on the data sheet?

    Any help would be greatly appreciated.

  2. #2
    Forum Expert xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    3,958

    Re: Pivot Filter not allowing filter by year, month and date

    Hi,

    I guess you grouped the Due date field in the pivot table? Assuming you did, the year is now a separate field. (filters in pivot tables simply do not work the same way as those in worksheets)
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Registered User
    Join Date
    02-17-2017
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot Filter not allowing filter by year, month and date

    Hi nitwit,

    Yes, as per screenshot I have dropped the due date field in the filter section of pivot (see right hand side of screenshot on original post.

    I have also tried to use the year function in the filter field on pivot however if I want to look at what's due from 2016 and 2017 up to end of Feb for example I cannot do so using this method as if I untick March, April etc it excludes any amounts with a due date of March or April 2016 as well as 2017 thus not giving an accurate picture of what's due. My boss is not too hot on excel and knows basics such as filters etc. hence my reasoning for trying to do it that way. If you can suggest an easy method to allow him to quickly display what's due by currency for whatever period he choses it would make my job (and life) a hell of a lot easier.

    Thanks in advance

  4. #4
    Forum Expert xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    3,958

    Re: Pivot Filter not allowing filter by year, month and date

    Broadly, for 2010, you have three non-code options

    1. Add a formula column to the source data that tests if the date matches your date criteria- for example you might use a start and end input cell- and returns a simple TRUE/FALSE value. You can then filter for TRUE in the report filter. Unfortunately you will need to refresh the data when you change the criteria.

    2. Add the date to the row filter area, which will re-enable regular date filtering options but will of course add an additional grouping level to your pivot table, which I suspect is not what you want.

    3. If the pivot layout is fairly static, you might use a table of formulas rather than a pivot table, as it is easier to make that reflect dynamic date criteria.

    If you can use code, then it would be fairly simple to automate the data refresh for option 1 for example.

  5. #5
    Registered User
    Join Date
    02-17-2017
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot Filter not allowing filter by year, month and date

    Thanks again Nitwit, unfortunately my boss works away a lot (far east and hence different time zone to me). He would like the option to simply see what is due at any particular date, there is no rhyme or reason to what date he chooses. He sometimes would like to see what's due up until Tuesday, then want to know if we pay that balance on Tuesday, what's due up until Thursday. He can work filters so he would tick the dates upto Tuesday, then untick those and tick Wednesday and Thursday to get the answer but that's about as far as his excel skills muster I'm afraid. I cant lie however, my excel skills are moderate at best however I've often found I can achieve most results I need with basic formula's and/or pivots, however coding (I assume you mean VB coding), is beyond me.

    To avoid me getting the dreaded 11pm phone calls I was trying to build a "fool proof" simple model for him whereas I can upload an updated Aged Creditors into the data sheet and he can then go in and refresh pivot and filter away on whatever dates he likes at his hearts desire without having to bother me.

  6. #6
    Forum Expert xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    3,958

    Re: Pivot Filter not allowing filter by year, month and date

    When I say "if you can use code", I mean that if code is acceptable, we can produce some for you. It would require an example workbook matching your layout (censored data).

    If you can persuade your boss to upgrade to 2016, you could use a timeline.

  7. #7
    Registered User
    Join Date
    02-17-2017
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Pivot Filter not allowing filter by year, month and date

    We may well be using 2016 as there is a timeline function however again this doesn't allow you to pick specific dates (quickly and easily anyhow) from what I can see.

    I will have another look next week to see if I can build a sample workbook (I believe simply changing supplier details may be all that is required). Would this be chargeable??

  8. #8
    Forum Expert xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    3,958

    Re: Pivot Filter not allowing filter by year, month and date

    If you set it to day display you can simply click a day. It's then fairly easy to expand a day or two either way if needed.

  9. #9
    Forum Expert xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    2010
    Posts
    3,958

    Re: Pivot Filter not allowing filter by year, month and date

    Quote Originally Posted by Steve aka Munky View Post
    Would this be chargeable??
    No, not at all.

    There is, as I understand it, a commercial section to this forum but I do not have access to that. However, assuming this doesn't turn into a full-blown project, I'm quite happy to knock up some code for you. If nothing else, it may provide food for thought!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1