+ Reply to Thread
Results 1 to 2 of 2

Apply different date filters to specific column values in a pivot table

  1. #1
    Registered User
    Join Date
    01-24-2021
    Location
    Toronto,Canada
    MS-Off Ver
    Excel 2016
    Posts
    3

    Apply different date filters to specific column values in a pivot table

    Suppose I have the following pivot table from one data source
    edb9afcecb8ba2370bef7b2e1146b13d.png

    The source data is essentially a spreadsheet of accounts which indicates 3 items (3 columns)- date account was created, date account was successful, date account was bought.

    There are 3 stages an account goes through: step 1 it gets created. Step 2, if the employee deems the account to be successful, they mark it as successful and mark the date it became successful. If there is more interest in the account, the account is bought and the company receives money for it - they mark the date it was bought.

    If you look at the data, the # of created accounts > # of successful accounts > # of bought accounts.

    However, a created account can be made in Jan 2021 for example, but can be successful in Feb 2021 and then bought in June 2021. An account cannot necessarily be created, be successful, and be bought all in the same month/year.

    That's the problem I have with this pivot table - the values field I have is using the count of date created, count of date successful and the columns on my pivot table is grouped by quarters of Y2021. The rows of the pivot table are the different regions the account belongs to. The problem I have is I am grouping the dates based on the date created and data is being filtered strictly from that date created filter; I don't know how to create a "global" date filter that is not strictly based on the date created filter

    So, in the February 2021 column for example, it is telling me I have 8 created accounts and 6 successful accounts, when there really should be 5 successful accounts. If you double click that cell which launches a table of that data, you'll see an account that was successful in August 2021, however, it was created in February 2021, so that is why it will say 6 successful accounts . That is an example of a discrepancy in my pivot table. If region3 has 10 created accounts in January 2021 for example, and in the same month/year, has 5 successful accounts (by looking at the source data) and one account becomes successful in July 2021, the pivot table should indicate that correctly as 10 created accounts in Jan 2021 and 5 successful accounts in January 2021.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,567

    Re: Apply different date filters to specific column values in a pivot table

    I feel that we may be in a better position to help if we could see a sample of the source data.
    The banner at the top of the page has instructions as to how an .xlsx file may be attached to a post.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to apply multiple and independent filters for pivot table values?
    By dejavu619 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-08-2021, 05:17 AM
  2. [SOLVED] Clearing Specific Pivot Table Filters
    By vwhite in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-19-2020, 11:44 AM
  3. Replies: 4
    Last Post: 07-01-2020, 02:13 AM
  4. [SOLVED] Macro to select multiple pivot table filters, based on values in a table
    By Portuga in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 12-03-2018, 06:42 PM
  5. Controlling pivot table filters via specific cell
    By KevinThomas in forum Excel General
    Replies: 12
    Last Post: 05-23-2014, 06:05 AM
  6. How to apply multiple label filters on pivot table at once
    By babbeleme1 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-27-2013, 02:16 PM
  7. Replies: 1
    Last Post: 02-22-2012, 12:21 PM

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