+ Reply to Thread
Results 1 to 11 of 11

Establishing Multi-Criteria Template for Charting

  1. #1
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Question Establishing Multi-Criteria Template for Charting

    Hi,

    I'm trying to establish a template that will do the following:
    • Read/Count data that falls within specified dates - Either weekly &/or Monthly
    • Find its task owner & the following:[INDENT]How many tasks assigned
      Within given week &/or month also find status if "On-Time", Day(s) Late & total number of tasks[INDENT]

    Off my task log, I'll should have or trying to establish the following breakdown results:

    Multiple task owners
    4 Primary Task statuses of:
    Total tasks assigned (no date criteria)
    Total tasks assigned within given date criteria (weekly &/or monthly)
    Status of deliverables - "On-Time" or "Late"



    Does anyone have a good matrix type of template that works well for charting? If so, please provide example template (with chart).


    Thank you.
    MyCon
    -- Using Latest Version of Excel

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Establishing Multi-Criteria Template for Charting

    Hi Jason,

    Did you know we work for free? You are asking a lot of us without showing us what you have already.

    As I read you question I kept imagining a Pivot Table Chart tha had filters. You can group dates by day, month, quarter or Year using Pivot Tables. You can even group by number of days. You could filter by tasks and show Owners or filter by owners, depending on how you arrange the Pivot Table List data.

    Start by looking here
    http://peltiertech.com/Excel/Pivots/
    http://pubs.logicalexpressions.com/P...cle.asp?ID=553
    http://www.contextures.com/xlfaqPivot.html

    See if any of the examples get close to your needs. I think you will find you need to learn Pivot Tables and Pivot Charts.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Establishing Multi-Criteria Template for Charting

    Hi Marvin & Others,

    No & Yes - I know some here volunteer their services & examples. I don't or didn't know if moderators also volunteer their services & examples.

    As for my question - I know it appears to be a "loaded" question & perhaps it is. I would like to submit an example, but I need to remove company info. first. The way I have my file set up now, I would have to remove or redefine a lot of company info. I'll work on this & perhaps will submit shortly.

    Let me try to re-phase what I am trying to do:

    I'm trying to track a dozen workers delivery statuses on a week-to-week &/or month-to-month basis & determine if individuals are delivering "on-time", "late" or may be over-loaded with work.

    My main criteria or parameters to track are:

    Individuals - 12 - 15
    Duration - Weekly &/or monthly
    Task Deliverable status - "On-time", "Late" & perhaps, some others, but I'll keep it to these to begin with

    Once defined, be able to chart it, which I should be able to do. This information will be extracted from a log sheet that I already created.

    My issue is that I can define 2 parameters well, but struggling to get in the 3rd parameter into a "clean" table format & then be able to chart these results. Additionally, I would like to have all this defined simultaneously both in some type of table format & in a chart.

    Just curious if anyone out here was trying or did something similar & how they defined or created their spreadsheets & charts. Examples would be appreciative.


    Thanks

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Establishing Multi-Criteria Template for Charting

    Hi Jason M.

    How about looking at http://office.microsoft.com/en-us/te...010152732.aspx for a while to see if any one of the existing Office Templates fits your data.

    I'm still thinking with some sample data an easy Pivot Chart would do the trick.

  5. #5
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Establishing Multi-Criteria Template for Charting

    Hi Marvin,

    I'm not familiar with Pivot charts. Perhaps this could work. Do you have any examples?

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Establishing Multi-Criteria Template for Charting

    Hi Jason,
    Look at the links in my post to you, above.

  7. #7
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Question Re: Establishing Multi-Criteria Template for Charting

    Hi Marvin & Others,

    Perhaps, this will help explain what I am seeking. I am seeking some type of chart table that will extract data from my log sheet - Task Log & summarize / calculate totals by:

    ● Date Duration - Either Weekly &/or Monthly
    ● Supervisors & Associated Engineer or vice versa
    ● Deliverable status - # of Tasks assigned, broken down to week review duration (weekly or monthly), Tasks completed "On-Time", Tasks completed "Late"

    In my original table format that I created, the intent was to extract data from the log sheet on a weekly basis. I did this successfully.

    For the most part, I have what I want in terms of data extraction results, but now want to have a continual type of table format & chart.

    See example (Excel 2007) file & Worksheet: Weekly Metrics chart table.

    Feel free to re-arrange my table to get better chart format.

    If possible, I rather have 1 Overall chart overview, but 2 or 3 are o.k.

    Thanks

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Establishing Multi-Criteria Template for Charting

    Hi mycon73,

    My advice is to see what Pivot Tables can do for you. I've created two simple pivots on your middle sheet. They may not be enough but you should explore them more. I see that you have a complex design and I can't help much more without a lot of effort.

    As you discover what Pivots can do I believe you will rearrange your tables to suit your outcome needs.

    See all pages of http://office.microsoft.com/en-us/ex...010205886.aspx

  9. #9
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Establishing Multi-Criteria Template for Charting

    Hi Marvin,

    I don't think I want to use Pivot tables here because I'm trying to show results simultaneously through some type of matrix table & chart.

    The initial format that I have in my Weekly Metrics worksheet works well between the specified dates given - In this case for a week's duration. Now, I'm trying to expand it so it's an expanded data collection table that is on a continual week-to-week or month-to-month basis & be able to chart this.

    Is this possible?

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Establishing Multi-Criteria Template for Charting

    Hi mycon73,

    After looking at your charts earler today I figured you were way ahead of me understanding what you were doing. The charts look great at first glance.

    Then I looked at your VBA code and find 33 modules and only find code in 33. You can remove all those empty modules to make things easier.

    I know it is easy to group using pivot tables and that is why I suggested it. If you like your format and don't want to make a table I think you are on your own.

    Maybe one of the smart guru's will look at this and have a suggestion.

  11. #11
    Valued Forum Contributor
    Join Date
    07-16-2007
    Location
    GA
    MS-Off Ver
    Office 365 - Version 2403
    Posts
    1,052

    Re: Establishing Multi-Criteria Template for Charting

    Hi Marvin,

    Yes, it does appear to that I will be on my own. I was hoping I could group Weeks, Task Status & Position Statuses (Supervisors or Engineers) altogether in one nice type of table matrix & then chart that.

    Adding in that 3rd element or criteria seems to have its restrictions or may be impossible.

    Thanks for trying & giving suggestions. I think suggestions may lead to something else that I may be able to use & will explore that.

    Thanks again....

+ 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