locked
Using a KPI to measure the progress between two dates RRS feed

  • Question

  • Hi all,

    Hope someone can advise me on this, I'll try and be as clear as possible.

    I have a custom list - "Requests". It's going to be used to log requests by an IT Helpdesk.

    The manager of the Helpdesk wants to be able to see how far through a particular request the user has gotten.

    The request has two columns: Date Created and Date Due

    Date Created is a basic Date column, set to "Today" as the default entry
    Date Due is another basic date column, where the user selects the due date from the standard drop-down calender picker

    The manager wants to see a traffic light that goes from:

    GREEN (the request has only just been logged and is less than 50% of the way through between start and due date),
    AMBER (the request has gone beyond more than 50% of the way through between the start and due date)
    RED (the request Due Date is imminent - say, 90% of the way through the date period)

    How can I achieve this?

    Thanks in advance!

    Duncan
    • Moved by Mike Walsh FIN Sunday, February 7, 2010 3:12 PM kpi q (From:SharePoint - General Question and Answers and Discussion)
    Friday, February 5, 2010 2:18 PM

Answers

  • You can just leave it as a single line of text.  You just need it as a reference when you setup the configuration.  Once you have it included you can delete the Today column.  If you ever need to modify the formula you would need to add it back in.  Refer to the first link I included.

    Just remember that this will produce a static result and will only update when you edit the list item.  If you want something dynamic you will have to go with a computed column or the custom dataview option.  Refer to the other links I provided for those options.


    • Marked as answer by Duncan McDonald Monday, February 8, 2010 12:00 PM
    • Edited by Mike Walsh FIN Friday, February 12, 2010 4:29 AM sig removed - flash
    Monday, February 8, 2010 11:36 AM

All replies

  • You can create a calculated column in the view to display the information.  You need to create the dummy Today column to reference for the calculation to put in for the formula:

    =DATEDIF([Date Created],[Today],"d")/DATEDIF([Date Created],[Date Due],"d")

    Now the downside to a caclulated column is that the value is static and will only change when you edit the list item.

    An alternative would be to do a custom dataview.

    Take a look at the postings below for some additional guidance:

    http://www.apptheory.com/sharepoint/sharepoint_blogs/articletype/articleview/articleid/158/calculated-columns-vs-today-amp-me.aspx

    http://blog.pathtosharepoint.com/2008/08/14/calculated-columns-the-useless-today-trick/

    http://blog.pathtosharepoint.com/2008/08/25/a-countdown-for-tasks-lists
    /



    • Edited by Mike Walsh FIN Friday, February 12, 2010 4:29 AM sig removed - flash
    Sunday, February 7, 2010 5:45 PM
  • Hi Dan

    Thanks for that - I see where you're going with the calcuation.

    Forgive my ignorance, but what format am I doing the column "Today" in? A number field? Date?

    I created the Today column as a date with "Today" as the pre-selected entry, and I seemed to get it working - it showed one day left for a sample entry I'd created (which was accurate). But as soon as I'd edited the dates, changing them to different start and end dates, the calulated field reported a "#NUM!"

    Am I missing something?

    Duncan
    Monday, February 8, 2010 10:57 AM
  • You can just leave it as a single line of text.  You just need it as a reference when you setup the configuration.  Once you have it included you can delete the Today column.  If you ever need to modify the formula you would need to add it back in.  Refer to the first link I included.

    Just remember that this will produce a static result and will only update when you edit the list item.  If you want something dynamic you will have to go with a computed column or the custom dataview option.  Refer to the other links I provided for those options.


    • Marked as answer by Duncan McDonald Monday, February 8, 2010 12:00 PM
    • Edited by Mike Walsh FIN Friday, February 12, 2010 4:29 AM sig removed - flash
    Monday, February 8, 2010 11:36 AM
  • Hi Dan

    Good stuff. I've got to the stage now where I can get a percentage output between the start and due dates (that if course, if a start date of the 4th, an end date of the 10th and the current date of the 8th = 66% of the way through!)

    If it does, then I can knock up some KPI's to monitor that column.

    Thanks very much for your assitance.

    Monday, February 8, 2010 12:00 PM