+ Reply to Thread
Results 1 to 8 of 8

DIFOT Dashboard

  1. #1
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Excel 2016
    Posts
    104

    DIFOT Dashboard

    Hi Experts
    I am trying to create a DIFOT dashboard and need some help
    I need help with creating a Performance Score Overview, what that is when I click on Oct, it shows how many lines were late by 1 day, how many lines were late by 2 days and so on, then another Performance Score Overview and it shows how many lines were early by 1 day, how many lines were early by 2 days and so on
    Then something that shows in month how many average days late and one more that shows on an average how many days early

    I need it in pivot table, so when I click on a month it only shows the data for the month
    How do I link the timeline to a slicer? So, when I click on a month in the timeline it only the days for that month in the slicer?
    How do I add a year pivot table, so I can click the year, then the month and then the day?
    I have tried to create pictures of what I am trying to achieve in the attached excel file

    Thanks in advance
    Rahul
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: DIFOT Dashboard

    I'm not sure you can link a time line to a slicer - at least not without VB code. The attached uses helper columns to compute the year and the month and slicers on Year, Month and Date.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Excel 2016
    Posts
    104

    Re: DIFOT Dashboard

    Hi dflak
    Thanks for that, the helper coulumns work

    Can you help with creating a Performance Score Overview, what that is when I click on Oct, it shows how many lines were late by 1 day, how many lines were late by 2 days and so on, then another Performance Score Overview and it shows how many lines were early by 1 day, how many lines were early by 2 days and so on
    Then something that shows in month how many average days late and one more that shows on an average how many days early

    Thanks

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: DIFOT Dashboard

    I suspect this would be another pivot table. However you have two different columns with Early / OnTime / Late status: Columns O & P. Which do you want to use? Columns Q & R have Late / OnTime status - do you want to do anything with them?

  5. #5
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Excel 2016
    Posts
    104

    Re: DIFOT Dashboard

    Hi dflak
    I need to use Columns Q & R please

    Thanks

  6. #6
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: DIFOT Dashboard

    I set up a pivots sheet with additional pivot tables driven by the same slicers. The layout is ugly, but I think this is what you want.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    02-23-2014
    Location
    Melbourne
    MS-Off Ver
    Excel 2016
    Posts
    104

    Re: DIFOT Dashboard

    Hi dflak
    Thanks for that

    It's awesome. I will work on the layout

    Thanks for your help

    Just a question, the below formula works, but shows #N/A, what do i need to change to show the blank results as 0

    =IFERROR(VLOOKUP(B58&"",Pivots!$J$4:$K$15,2,FALSE),NA())

    Thanks
    Much Appreciated
    Rahul

  8. #8
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016
    Posts
    4,390

    Re: DIFOT Dashboard

    =IFERROR(VLOOKUP(B58&"",Pivots!$J$4:$K$15,2,FALSE),0)

+ 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