0

I have 3300 road inspection records in an Oracle database.

I've summarized the inspection records in a SQL query as follows:

YEAR    AVG CONDITION   CLASS   ROAD_COUNT
----    -------------   -----   ----------
2013         8.0          A        300
2014         7.8          A        300
2015         7.3          A        300
2016         7.3          A        300
2017         7.1          A        300
2018         7.1          A        300
2019         7.1          A        300
2020         6.8          A        300
2021         6.5          A        300
2022         5.9          A        300

2013         9.0          B        500
2014         8.8          B        500
2015         8.3          B        500
2016         8.3          B        500
2017         8.3          B        500
2018         8.2          B        500
2019         8.1          B        500
2020         7.8          B        500
2021         7.5          B        500
2022         6.7          B        500

2013         8.5          C        2500
2014         8.3          C        2500
2015         7.7          C        2500
2016         7.8          C        2500
2017         7.7          C        2500
2018         7.6          C        2500
2019         7.6          C        2500
2020         7.2          C        2500
2021         7.0          C        2500
2022         6.2          C        2500

I've served up the query (a database view) to Excel 2016 via an ODBC connection and Microsoft Query:

enter image description here

Question:

It's difficult to see patterns in the data when it's in table form. I want to visualize the data in a chart in Excel so that my users can make sense of the data.

What's an appropriate way to visualize this kind of data in Excel?

For example, is a line graph a suitable choice?

enter image description here

Improve this question
1
  • 1
    Although data visualisation is part of the user experience, you might get better answers at Cross Validated.
    – locationunknown
    Commented Aug 5, 2022 at 4:45

1 Answer 1

Reset to default
2

I don't think the average condition is good enough. Don't you have the exact condition of all streets? Wouldn't the user want to know if there is outlier data, like some amount of streets who are in really bad condition. Maybe.

You could go for a stacked bar chart. Example for one year:

enter image description here

Improve this answer
3
  • That's a very interesting idea! Thanks. Similar to what you suggested, we have ranges of conditions that correspond with certain construction activities. So, for example, your green bar could be Good/No construction required; yellow could be minor maintenance required; red could be major maintenance required. That kind of information is very relevant to our business. [continued]
    – User1974
    Commented Aug 6, 2022 at 2:47
  • So I think I'll use that idea, but in a separate chart, since I think a "100% Stacked Bar Chart" would be most appropriate. I wouldn't want to use a non-100% Stacked Bar Chart, because the decreasing condition/decreasing bar size might be misleading...it would be confusing to see the bars shrink in size over time due to the average condition going down over time. Related post here: Stacked bar chart to show asset maintenance types by year. Thanks again, this was a great idea!
    – User1974
    Commented Aug 6, 2022 at 2:47
  • Happy to help :)
    – peq
    Commented Aug 8, 2022 at 5:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.