Question
Record Date when Scorecard Changes / Accounts Red over xx Days
Our overall goal is to have a report that shows Accounts that have had an overall score of Red for 60+ days. My first approach was to use Our "User Adoption" object, where the scorecard rating is being written each week. The issue here is that there are multiple records for each account; for example, Acme Corp will have 12 lines on the report for 12 weeks, each stating the account was "red." I can't figure out a way to get the report to show only those accounts where it's been red [i]continuously for 60+ days.
My next approach was to use Account Scorecard History, but I hit the same problem; there's no easy way I can see to look at the snapshot date and tell the report, "only show me accounts that have been continuously red in the snapshot for 8+ weeks." See screenshot for details (each colored block is the same customer, appearing multiple times).
I know I can do this in Salesforce with a workflow, but I'd rather not (we wouldn't get historical data). Is there a way to maybe write a date when the score changes, but also have it work for historical data? Thanks!
My next approach was to use Account Scorecard History, but I hit the same problem; there's no easy way I can see to look at the snapshot date and tell the report, "only show me accounts that have been continuously red in the snapshot for 8+ weeks." See screenshot for details (each colored block is the same customer, appearing multiple times).
I know I can do this in Salesforce with a workflow, but I'd rather not (we wouldn't get historical data). Is there a way to maybe write a date when the score changes, but also have it work for historical data? Thanks!
