Help with Historical Health Score Reporting/Dashboard

Hi @veecampell 

Can you clarify one thing: do you want to compare W-2 with W-1 or identify anything that changed at any time during last week?

I’d recommend picking a fixed day of the week (first day) to compare with the same fixed day the week before (and the same for the month - last day). Because technically, the score is modified as often as your scorecard update rules are running (likely, on a daily basis), so the “score modified at” field wouldn’t be accurate, and your current and previous score could be the same.

I would suggest using a data design that could roughly look like:

• Fetch Task from Account Scorecard History 
  • Company ID (and any other company attributes)
  • Overall Score (Account), rename this to This Week’s Score
  • Snapshot Date, rename to This Week’s Snapshot
• Fetch Task from Account Scorecard History
  • Company ID (idem above)
  • Overall Score (Account), rename this to Last Week’s Score
  • Snapshot date, rename to this Week’s Snapshot
• Merge Task on company ID (inner merge since your accounts need to exist in both data sets to show a change)

Since snapshots are taken with the following sequence:

• Weekly on the first day of the week
• Monthly on the last day of the month

You shouldn’t even have to add filters into your fetch tasks. And you can only go by these.

Since you have many different reports for different conditions, I would add filtering on reports only so you can use the same data design for all of this.

For example, for companies that have changed from red to green, your filtering at a report level would look like:

• This week’s score = 80 (if this is the number that is equal to green in your instance)
• Last week’s score = 20 (idem above, that depends on what value equates to red in your instance)

You could also add a filter on time granularity = Weekly (or Monthly) for cases where the first day of the week and last day of the month fall on the same day to avoid confusing DD and ending up with two rows of data .

I hear you on a dashboard for everything, not sure you’d want everything in one table, really. But if you did, you could use global filters on your dashboard to pick the previous and current score of your choice (instead of having a report for each combination of drop and improvement). That would mean your table would show you anything that changed between current and previous week, regardless of which direction it went.

I personally have a notification setup to go out when the score drops from one day to the next, so my setup is simpler - not sure it’s possible to do something simpler than the above in your case.

Hope this helps.

Spot on @alizee. I pretty much sure the same approach.