I'm building a scorecard for a Sales team and am looking to create some KPI's. One thing I want to grade them on would be their timeliness in submitting customer promotions. Oftentimes we receive a deduction and there is no promo in the system. We track the promo creation dates and the date we receive the deduction. If the promo creation date is greater than the deduction date we know they didn't do their job on the front end. How could I go about creating some sort of visual to use on a scorecard? (I'm thinking a gradient from green to red and a percentage)  

Any help is appreciated

Thanks

I would start by doing essentially a DATEDIFF on the two dates coupled with an IF statement that checks if the promo creation date is null. So, in pseudo-code:

IF (promocreationdate = NULL, 99, DATEDIFF(promocreationdate, deductioncreationdate))

The Excel function is DAYS.  You should then have a number where no promocreationdate gives a very high number and otherwise you have a positive or negative number that corresponds to how fast they got their promo in, or not.  Something along those lines should work to generate a heat map or KPI off of.