Discussion


Note: Images may be inserted into your messages by uploading a file attachment (see "Manage Attachments"). Even though it doesn't appear when previewed, the image will appear at the end of your message once it is posted.
Register Latest Topics
 
 
 


Reply
  Author   Comment   Page 1 of 2      1   2   Next
jeanmallo

Registered:
Posts: 7
Reply with quote  #1 
ChAT_Example.PNG 

Hi all! I am hoping to get some feedback (good and bad!) on my dashboard.

Please see below for context.

 

I work within the children’s social care service in the UK, which has an unannounced inspection once every three years or so. The service is required to provide a comprehensive dataset within 24 hours of this unannounced inspection. The structure and detail of the dataset has been outlined in an Excel template by the inspecting body. Most local authorities* routinely produce this dataset, which spans across 11 child-level data lists in Excel that cover all areas of children’s social care. However, analysis is difficult due to the size and scope of the dataset at a time when local authorities’ resources are diminishing.

 

I have developed a tool built within Excel that aggregates and visualises all aspects of this dataset across 20 pages or so, and attached is an example of one of those pages (dummy data). Each section or relevant information is summarised on one page where possible, and this example shows the summary of List 3. The tool pulls together the local authority’s latest data as provided in the inspection dataset, along with published statistics for the last 5 years so as to provide benchmarking against statistical neighbours and the England average.

 

In my local authority we run this tool once a month, but this varies from once a week to once a quarter between local authorities. The frequency is largely dependent on when the local authority had their last inspection.

 

Users are familiar with terminology and abbreviations, but for those who want further information on definitions and calculations there is a Help section in the Excel workbook.

 

The tool has been developed within Excel, because local authorities all have access to it, it’s free, and users have an understanding so they can do troubleshooting themselves. It also means that pasting from Excel into Word is easy and straight forward.

 

*There are 152 local authorities in England that are required to produce this dataset. We share this tool with all local authorities for free, and 84% have requested access so far.

 

Thank you for taking the time to read this, and thank you in advance for those of you who will provide useful feedback.

 

Jean Mallo


sfew

Moderator
Registered:
Posts: 838
Reply with quote  #2 
Hello Jean,

Thanks for sharing this important work with us. There are many ways in which this can be improved, but I don't want to make any suggestions without first fully understanding what you're trying to communicate with each section. Please provide a section by section desciption of this report. Describe the data, why it appears in the report, and what decisions it might drive.

Technically, this isn't a dashboard--at least not based on my definition of the term. A dashboard is an information display that's used for rapidly monitoring what's happening on a regular basis. This is a report that's prepared after an inspection every three years or so. Unlike a dashboard, it isn't necessary to put everything on a single screen. What's important is that information that must be seen together (e.g., to make comparisons or see relationships) is displayed together on a single screen. There's no harm, of course, in putting all of this on a single screen, just as long as you can do so without sacrificing anything worthwhile that could have been better displayed if you had more space.

__________________
Stephen Few
jeanmallo

Registered:
Posts: 7
Reply with quote  #3 
Hi Stephen, thank you for your quick response.

I acknowledge your comment about the term 'dashboard', I guess 'report' adequately describes this piece of work. It is produced on a monthly bases, and provides a regular snapshot of what's happening across the whole service.

Having one page per section has actually shown to be really helpful at providing users with information at a glance. But you're right of course, some sections have been split into two or three pages as appropriate so that important details have the space to stand out.

The report's structure starts with an initial contact to children's social care, and moves through the journey of a child where the contact is considered serious enough to be a referral to social care for services. A referral then progresses to an assessment that should usually be completed within 45 working days. At the end of the assessment the decision is made for the child to stay with the family with no services, or with some services / intense services from social care. Or it is decided that the child will be taken away from the family and taken in to care where the local authority has the parental responsibility of the child.

Each of the sections is one aspect of a child's journey through the social care system. This page shows the very beginning of the child's journey. There has been a contact made to the local authority, and this can be from an individual (the child themselves / a family member / a neighbour) or it can be from health, the police, etc. And it has been decided that it will be referred to social care services.

The source of the referral shows where our children are coming from compared to other local authorities similar to us, or the England average. For example, on this report the proportion of referrals from schools is much lower in the last 3 months than for comparator data. Is this because it is a seasonal dip, or do schools in the area not know what they can refer to social care, or are we dealing with child protection issues early on before it takes a school to recognise the problem and thus it's coming from another source initially.

The age/gender and ethnicity breakdowns provide local authorities with an understanding of what groups of children are over-/under-represented at each stage of social care. For example, on this report there is a peak for 15 year old boys, whereas girls aged 13-17 are over-represented.

The rate of referrals show whether the activity for their local authority is the same as for others and the England average. The snapshot of the last 3 months shows that the activity has been much lower than what would be expected in an average 3 months of the year. Probably seasonal, but could it indicate something else.


Referrals with no further action are those where they were referred to social care, and the local authority makes the decision that social care services are not needed. High percentages would indicate that many referrals are progressed inappropriately when they should have been sign-posted elsewhere.

And finally, a re-referral would be where a child had a previous referral in the last 12 months but their case was closed and now they've come back again. It's normal to have some re-referrals, but if this is very high then it suggests that the local authority is not effectively making a difference in the child's life before closing the case as the child is still in need within a year. There are obviously many reasons a case can be closed and re-opened, such as a family moving away and moving back. In London re-referrals would be lower because boundaries between local authorities are much smaller and families can easily move from one to another, whereas in other parts of England the boundaries are much bigger and even if a child moves 100 miles they're still in the same local authority area.

I have not gone into detail for the other sections of the report, but I hope that what I've provided above gives an idea of the information shown per page. Please let me know if you have any other questions.

Thanks
Jean
sfew

Moderator
Registered:
Posts: 838
Reply with quote  #4 
Jean,

Here are a few comments and recommendations:
  1. The information that you provide in the second row (i.e., the title of the screen and the from and to dates) could be incorporated into the top row with ease.
  2. With the second row freed up for other information, it might serve as a good place to display a few key figures, such as "723 children were referred, of which 13% are re-referrals and nn% require no further action."
  3. In the Source of Referral section, I don't know what "16-17" means. I would assume that these are ages, but it doesn't seem to make sense to compare all of your referrals for children ages 0-17 to only 16 and 17 year olds in the other groups (e.g., England as a whole). This graph would work better if the sources of referral were in order from the greatest contributors to the least. A fundamental problem with the data in this section is that it lacks historical context. How can people tell if the breakdown of referrals by source has changed over time?
  4. In the Age and Gender section, a frequency polygon with a single quantitative scale along the X-axis and a line for males and a line for females would be easier to read. It would especially make it easier to compare male to female quantities. The meaning of "Proportional to 0-17 population" lines and how to read them isn't obvious. Using a much line color to represent females suggests that they are less important than males. Similar to the previous section, this section lacks historical context.
  5. In the Ethnic Backgrounds section, it would work better to sort the groups from highest to lowest values. This section also lacks historical context.
  6. The donut chart, which is always a poor form of display, reports a single number: 13% re-referrals. As I suggested above, this chart could be eliminated by stating this information as one of the key figures that are featured at the top of the report.
  7. In the "Children with previous referrals..." section, displaying the data graphically doesn't seem necessary. A simple table should work just as well and would require less space.
  8. The three line graphs at the right-hand side of the report all represent the same three categories (i.e., LA, England, and SN) and the same time period, but they are not aligned with one another. I would be useful to align them so that comparisons between these graphs, which could be useful, can be more easily made. None of the line graphs display data for "2017-16 prov," which makes me wonder why it's there. There is no reason to make SN look considerably different from the other two categories by using a dashed line. A solid line of a different color would work better. In the top line graph, the Y-axis title is incorrect. You are reporting a count, not a rate.
  9. You don't need a bar graph in the NFA section. Simply stating the percentage of referrals that don't require further action seems sufficient.
This is not a comprehensive review, but it should be sufficient to improve the report.

__________________
Stephen Few
jlbriggs

Registered:
Posts: 200
Reply with quote  #5 
Jean, a couple of clarifying questions/assumptions -

Quote:
Originally Posted by sfew
  1. In the Source of Referral section, I don't know what "16-17" means. I would assume that these are ages, but it doesn't seem to make sense to compare all of your referrals for children ages 0-17 to only 16 and 17 year olds in the other groups

I struggled with this as well, but made the assumption that they referred to years 2016-2017, perhaps similar to a school year.

In this same chart, I am not completely sure who/what the Local Authorities refers to. Is this the same organization(s) that the Last 3 Months refers to, but for a different time period?

I am also assuming that the "Proportional 0-17 Population" line is a reference to the overall population, to gauge whether an age group is over/under represented.

I am also very curious as to what makes a 'statistical neighbor'. Is this something pre-defined outside of your organization, perhaps by demographic data for the region or other factor? Something defined dynamically based on this data? I am in part skeptical of its relevance, and in part intrigued by usefulness if it is in fact relevant.
jeanmallo

Registered:
Posts: 7
Reply with quote  #6 
Stephen, thank you very much for taking the time to give such detailed and helpful examples of how this report could be improved, it is much appreciated. I have taken everything on board, in particular the Age/Gender chart, which upon re-design reads so much better.

 agegender.png 


The 16-17 refers to the reporting year, and LA 16-17 and Last 3 months are both for the same local authority that would be using this report. Comments around this have been noted.


Each local authority has ten other local authorities that are statistically closest to themselves known as statistical neighbours. This is based on a complex model of contextual information that has been weighted accordingly (for example, deprivation, educational outcomes, ethnic backgrounds, number of single parent families, etc). It's a national model and is only updated once every ten years or so when the national census takes place. We always compare ourselves to our statistical neighbours, in addition to comparing to a national / regional average. Some local authorities (LAs) also compare themselves to a custom group of other LAs who's performance they would aim to match for example.

For the age/gender and ethnicity charts there is no available historical data. The ethnic groups are in the order they appear in national publications as I thought this would help compare the ethnicity from one section to the next. But I actually have an ethnicity summary page towards the back, so these groups can be sorted from biggest to smallest, same goes for the sorting of the referral source.

The referral source groups have the previous year's averages alongside the latest data as context. I wasn't sure about providing further historical data here for the last 5 years. The key objective for this chart is to see where the local authority stands out from comparators now, and whether this has changed since the last time figures were published. I.e. will it be a surprise to the inspecting body or not, does the local authority have a story behind the change.

Thanks again for your input. It really helps me to step back and look at it afresh again.

Jean
 

sfew

Moderator
Registered:
Posts: 838
Reply with quote  #7 
Jean,

Your redesign of the referalls by age graph is not exactly what I had in mind. A frequency polygon uses lines only--no bars. Imagine replacing the two sets of bars with two lines. This would make the patterns much easier to see and compare. I still don't know what the "proportionate" lines represent. I would assume that they represent the proportion of each age's referrals compared to the total number of children for each age, but that would require a percentage scale, which is not shown. If this is what you intend, it might work better to put this information in a separate graph with its own scale just below the referral graph rather than placing everything in a single graph with two scales.

__________________
Stephen Few
jlbriggs

Registered:
Posts: 200
Reply with quote  #8 
Jean, I did a couple of examples of how I would plot this data.

My thoughts on the Age/Gender chart are pretty much the same as Stephen's. I plotted the distribution as frequency polygons, and then added a second chart below that plots the difference in proportion between referrals and (what I assume is) the general population:

  ChAT 2.png 

(Post edited to update image following Stephen's suggestions regarding point markers)

I also took a stab at the Source of Referral chart. I tend to have a bit of a hard time with grouped bar charts in general, so I wanted to see the data as a dot plot.

I decided to highlight the 'Last Three Months' series, which I took to be the focus of the chart, and allowed the other series to be less impactful. I feel like this does a pretty good job of making comparisons easy for the user:

Chat 1.png 

I would prefer to do something better with the legend, but with an absence of time to go too far with everything, I left it as is.

One of my priorities in a chart is always de-emphasizing  gridlines, borders, axis titles, and anything else that can fall to the background or be removed.
In that vein, toning down the black of the titles to a dark grey, removing the light grey background between the charts, taking the gridlines *way* down, would be some of the actions that I would recommend for your report.

sfew

Moderator
Registered:
Posts: 838
Reply with quote  #9 
jbriggs,

You don't need data points along the lines in the frequency polygon, and certainly not large hollow data points that shout. The graph will look much cleaner without them. Also, to save space, arrange the items in the legend side by side horizontally rather than one above the other vertically.

You need to explain how to read the "Referrals by Age and Gender vs. Population" graph. It isn't obvious to me why you would have negative values when you're showing the proportion of referrals to the populartion per age. A proportion would always have a value between 0 and 100%. The fact that you have both positive and negative values suggests that you're showing a variance, but it isn't obvious what that variance is.

__________________
Stephen Few
jlbriggs

Registered:
Posts: 200
Reply with quote  #10 

Stephen - point taken on the point markers in the frequency polygon.

As far as the 'vs population' - again, I am making the assumption that the purpose of the original chart is comparing the demographics of referrals to the general population. This may be the wrong assumption, but it has not yet been clarified, so I went with it.

So my version of the plot is a variance, hence the title 'vs Population'.

sfew

Moderator
Registered:
Posts: 838
Reply with quote  #11 
jbriggs,

I don't understand your explanation of the "Referrals by Age and Gender vs Population" graph. The values should be proportions, not variances. To get negative values, you would have to subtract one value from another, but that doesn't seem applicable here. I assume what's desired is the percentage of children that are referred versus the total number of children for each age, not a variance. This would always result in a percentage between 0 and 100%.

__________________
Stephen Few
jlbriggs

Registered:
Posts: 200
Reply with quote  #12 
I took the data to represent this:
(making up the numbers) 10% of the female population is 5 years old; 12% of the female referrals are 5 years old; the variance is 2%.

Again, not knowing for sure what the data represents, I am running with assumptions. Being overloaded at work but still wanting to play someone else's charts, maybe I am not able to think it through thoroughly...but that was my goal.

sfew

Moderator
Registered:
Posts: 838
Reply with quote  #13 
jbriggs,

I now understand. I don't think this is what Jean wants, but I'm not sure. Jean will need to clarify this for us.

__________________
Stephen Few
jlbriggs

Registered:
Posts: 200
Reply with quote  #14 
A couple of updated images:

(removed markers from frequency polygons, updated legend layout)
ChAT 2.png 

(ordered by largest percent/last three months)
  Chat 1.png 

jeanmallo

Registered:
Posts: 7
Reply with quote  #15 
The population lines refer to the general population in the local authority area for all children aged 0-17 based on population estimates.

So for example, there were around 100 referrals for girls and boys aged under 1 years old, but if we were to compare this to the distribution of the general population, we would have expected there to be around 150 referrals for girls and 160 referrals for boys. In contrast, referrals for 11 year old boys is over-represented compared to the general population, whereas this remains under-represented for girls. Girls are only over-represented for the age group 14-17 year olds.
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.