Point Tracker System

Dan B.

Hello, I am wanting to build a system for tracking points. There are two kinds of points: good and bad. Simply put, I want to keep an up to date tracking for each employee. Here is what my starting base is:

Now here is where I am getting stuck. How do I make it so that I can reference a specific employee's total from a previous row? For example, in my chart, Steve was a bad boy on the 6th, so he gets minus 1 point which brings his total from 5 down to 4.

I am not opposed to creating helper sheets or columns. I'm just not sure what direction I should go in. Any help would be appreciated.

markkrebs

In Total Points column:

=SUMIFS([Good points]:[Good points], Name:Name, Name@row, [Point Event date]:[Point Event date], <=[Point Event date]@row) - SUMIFS([Bad Points]:[Bad Points], Name:Name, Name@row, [Point Event date]:[Point Event date], <=[Point Event date]@row)

This also makes it independent of how the dates are entered.

jessica.smith

@Dan B.

There are a number of ways to accomplish this. I would likely do the following:

1. Create a sheet with five major columns:

Name - use a contact type and make sure you have unique consistent employee names entered

Date - Date Type Column

Event - Drop down menu with both good and bad event options

Event Type - drop down menu with "Good" and "Bad" as options (or symbols). Used soley for being able to sort and filter data if needed. Would probably auto select with a column formula.

e.g., IF(OR(Event@row = "Event One", Event@row = "Event Two", Event@row="Event Three",Event@row="Event Four", Event@row="Event Five"),"Good",IF(Event@row="Event Six","Bad",""))

Event Points - Column formula that automatically enters + or - Points for each event type

e.g., IF(OR(Event@row = "Event One", Event@row = "Event Two", Event@row="Event Three"),3,IF(OR(Event@row="Event Four", Event@row="Event Five"),2,IF(Event@row="Event Six",-2,"")))

2. Then I'd create a report that is grouped by employee and I'd sum the Event Points column.

3. I'd then have a chart on a dashboard that shows me a visual of total points per employee.

Dan B.

https://community.smartsheet.com/discussion/comment/405404#Comment_405404

Hi Mark, this works, but there is a tweak I need to add to it. Basically, I don't want employees going below 0 points. So if they were at 2 points, and did something bad that was worth 5 points, I want them to go to 0, not -3. Then, when they get "good points" again (for example, 2 points), I would like them to be at 2 points, not -1.

Is that possible?

And then the final level of this tracker is a unique tweak, which seems hard to do, but basically, If an employee gets a bad point within a week of a good point, I don't want it to count unless we check it off manually.

Dan B.

https://community.smartsheet.com/discussion/comment/405409#Comment_405409

Hi Jessica, what you provided is kind of what my current system is looking like. This point tracker is actually for tracking attendance and occurrences. So bad points are tardy, late, etc and good points are overtime they pick up.

The issue we are running into is that we have employees who will work a ton of OT and then have this huge bank of points where they then do as many occurrences as they want, since they are technically in the negative for their total points. So I need the chart to basically never go below 0 points. So I think the best first step is to uncouple the good and bad points.

Zachary Hall

While this could be done with formulas, if you already have the rest working, I wouldn't change it. You could set up an automation to trigger so that when a row is entered if the point value is below zero it resets the point total to zero.