Skip to main content

My team and I are trying to create a dashboard that shows historical changes in health scores and what measures caused the score to change. We are having a hard time filtering reports and simply want to know what went from:

  • Green to Yellow
  • Green to Red
  • Yellow to Green
  • Yellow to Red
  • Red to Green
  • Red to Yellow

Within the last week or month. I was wondering if there is a template or some guidance we can be given to show scorecard changes per account from week to week and month to month.

We were thinking of separate reports for each change, but are hoping there is a better way to display that information in a dashboard.

Any help with steps would be much appreciated🙂

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.

Thank you alizee! This is all very helpful and allows me to do so more thinking on exactly what I want to move forward with.

 

To answer your question, I basically want to see what scores moved from what to what and why, between 2 points in time. Whether that is week to week or month to month.

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

Reply


Terms & ConditionsCookie settings