+ Reply to Thread
Results 1 to 6 of 6

Pivot table and slicer help

  1. #1
    Registered User
    Join Date
    06-22-2016
    Location
    london
    MS-Off Ver
    2016
    Posts
    3

    Pivot table and slicer help

    Hi,

    I have created a pivot table with slicers. I can't quite get the month slicers working.

    Please see below table which records the number of days that people are absent:

    absense data.jpg


    The above formula under the total days columns calculates the difference between date from and date to, it excludes holidays which i've listed else where on the worksheet. This works perfectly.

    I'm having problems with below pivot table based on above data though. As you can see below i've created a date from slicer. But when I select just january for Jeff Banks, it shows 45 days. But 45 days spans Jan-Feb. I can't seem to display figures purely based on january. This deems the month slicer useless. Am I doing something wrong here? Any help appreciated as I'm banging my head on a wall

    pivot table.jpg

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Pivot table and slicer help

    The reason for this is:

    in your orginal data 01-01-2016 / 03-03-2016 the result is 45 days.

    After that you add the column (for the slicer) and tell them is is january for the whole period (which is wrong, since it is the period of 01-01-2016 / 03-03-2016).

    In that (this) case you have to split the data 01-01-2016 / 03-03-2016 into seperate rows for each period (eg. 01-01-2016 / 31-01-2016 etc).

    That is the way pivot table works.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    06-22-2016
    Location
    london
    MS-Off Ver
    2016
    Posts
    3

    Re: Pivot table and slicer help

    Hi

    thanks for the reply. it makes sense. Is there not a cleaner way to do this? the whole purpose is to report on per month. but by splitting the dates into different rows it seems counter productive

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Pivot table and slicer help

    define the period not january but e.g. differant, in that case you will know that is a period that is longer than a month.

    edit:

    to my knowledge there is no other easier solution.

  5. #5
    Registered User
    Join Date
    06-22-2016
    Location
    london
    MS-Off Ver
    2016
    Posts
    3

    Re: Pivot table and slicer help

    thanks, what do you mean by define the period? Would this be a different formula in the "total days" column?

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    13,764

    Re: Pivot table and slicer help

    cell f4 is e.g. Differant

    cell f5 is april

+ 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. Replies: 19
    Last Post: 04-04-2016, 02:58 PM
  2. Pivot Table Slicer Problem
    By skate1991 in forum Excel General
    Replies: 2
    Last Post: 02-28-2014, 09:54 AM
  3. Slicer Not Recognizing Pivot Table
    By BigBadBill in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-31-2013, 12:22 PM
  4. [SOLVED] Use slicer selections on one pivot table to filter another pivot table
    By porkandbeans in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 12:30 AM
  5. Pivot Table (Slicer) Help
    By skate1991 in forum Excel General
    Replies: 0
    Last Post: 10-11-2012, 11:52 AM
  6. Disconnect a pivot table from a slicer
    By LisaK in forum Excel General
    Replies: 5
    Last Post: 04-12-2012, 11:07 AM
  7. Pivot Table Slicer Limit?
    By zlehmann in forum Excel General
    Replies: 0
    Last Post: 11-21-2011, 12:35 PM

Tags for this Thread

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