+ Reply to Thread
Results 1 to 10 of 10

Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

  1. #1
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Lightbulb Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    Dear Forum Experts,

    Following up on my previous thread (which was SOLVED) for some more refinement to my Timesheet approach.

    I have attached a sample file with some data on the Timesheet & i need help on how to generate the right Pivottable & then Pivotgraphs.

    I have listed out points where i need support in the file. Please go through & request your kind support & help to achieve the desired results.

    Thanks,
    Mahesh
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!

  3. #3
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    Hi Pepe,

    Please dont get me wrong here; but i am unable to understand what is expected out of me?

    If it means that i didn't explain the scenario in my thread; then i apologize, but it was too much to write and hence thought an attachment with all explanations with arrows marked would help.

    Let me know action from my end on this, if any.

    Thanks,
    Mahesh

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

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    See if this fulfills the first two requests from the file:

    1 Need a graph which extracts Weekwise Deliverables along with the KPI against each of the delievrable:
    e.g - the list should extract only those tasks which are categorised as "Deliverable-format" or "Deliverable-no format" (See Col G)
    2 With the above list extracted; it should display the 3 KPIs in columns P, R & S - showing status for each delivery

    As modeled on sheet 2 the three pivot charts are linked to the slicer.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    Thanks JeteMc - It does fulfill the requirement; only except for the below conditions which could make it more meaningful:

    - Can we show the service group as well against the count of Deliverables (both for 'format' of 'no-format'). This will enable to show break-up of the count of deliverables distributed across the service groups

    - the above condition also should be applied to the KPI for "Right First Time"; for which the count of 'RFT' or 'Not RFT' should be distributed as per the service group to which they belong to.

    If you have any different idea; please let me know.

    Thanks so much yet again for your support!

    Mahesh

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

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    There was not an example of Deliverable-no format in the sample file. I changed the cell G6 on sheet 1, selected one of the pivot tables on sheet 2, selected the pivot table tools tab > analyze sub tab > refresh. At this point both Deliverable-no format and Deliverable- format appear in the slicer. Drag the cursor through both and the pivot tables will show the count of both conditions for all KPI's.
    Let us know if you have any questions.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    Thanks JeteMc....i should have done this myself. not worked with slicers before; hence sought your help.

    I think this is completed. Can you help me on one last thing about the below requirement:

    -I need a graph which shows Weekwise hours as stacked Bar Chart with - no. of hours on left side Y axis & on the right side Y axis display the % Utilization. This arrangement shows what are the billable hours per week & on the top it should float a line graph which displays what was the %utilization for each week.

    Let me know if this is possible with a Pivot graph or needs a separate table to be made?

    Thanks,
    Mahesh

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

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    Not sure what is supposed to be stacked, however I experimented with stacking task categories per week and was unable to get the graph to display % utilization.
    On sheet 3 is a column chart/line chart combo with a slicer to allow the user to choose the task categories to be studied.
    On sheet 4 is a pie chart with weeks placed in the filter field.
    Let us know if you have any questions.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    12-18-2015
    Location
    Mumbai, India
    MS-Off Ver
    MS Office 365
    Posts
    136

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    All Works perfect & as intended...Thank you very much JeteMc! This has indeed been a very helpful & learning experience for me & i appreciate your invaluable assistance throughout the past few weeks.

    I would be learning more on Pivot tables & then Pivot charts further to derive right information from a large data set.

    I would like to keep this thread open for the next 3-4 days; while i experiment & use your guidance to check my work; so that i can come back if i have still any questions.

    Thanks again!

    Mahesh
    Last edited by MaheshK5277; 02-06-2020 at 01:35 PM.

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

    Re: Timesheet summary for KPI graphs (Pivottable & Pivotgraphs)

    You're Welcome and thank you for the feedback. To mark the thread as 'Solved' use the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. After updating PivotTable Source data with Macro, slicer doesn't show PivotTable
    By mrdouglaswee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2018, 02:10 AM
  2. [SOLVED] Calculate Break deduction for timesheet summary
    By majorqt75 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-16-2017, 12:20 PM
  3. Replies: 1
    Last Post: 10-24-2014, 12:20 AM
  4. Create PivotTable Based On A PivotTable
    By goss in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 02-10-2014, 03:47 PM
  5. Copied a tab with a pivottable and pivottable graph
    By Dinger045 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-13-2012, 01:35 PM
  6. Shift rows below PivotTable down when PivotTable expands (PowerPivot)
    By hektisk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2012, 10:19 PM
  7. Timesheet Summary Page - Complicated
    By allan_j in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-10-2009, 05:09 AM

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