Thread by @InsightsMachine, 1. Last night I started working on a #Covid19 dashboard for New [...]

1. Last night I started working on a #Covid19 dashboard for New Zealand that scrapes data from https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation/covid-19-current-cases

Today I'm trying to work out how I can best serve up the data experience I want given the strengths, constraints of the tool. I'll live tweet thoughts as I go.

COVID-19 - current cases

Information about confirmed and probable cases of COVID-19 in New Zealand.

https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation/covid-19-current-cases

2. First up, I did a critique of existing charts and dashboards covering the NZ situation. I cut them into an Excel doc, with one tab for each provider. Here's a collection of vis from the page at https://www.stuff.co.nz/national/health/coronavirus/120622513/coronavirus-dashboard-key-graphs-about-the-spread-of-the-virus

3. Then I did a review. I tweeted some of this. Lots of viz that was intermediate at best. Opportunity lost.

Newsroom: https://twitter.com/InsightsMachine/status/1248723812947877889

Ministry of Health: https://twitter.com/InsightsMachine/status/1248843664656175104

ESR: https://twitter.com/InsightsMachine/status/1248836163286134784

Stuff (yet to do more critique): https://twitter.com/InsightsMachine/status/1248718645292748800

https://twitter.com/InsightsMachine/status/1248723812947877889

4. Next I tried to find the best place to grab data from. The NZ Ministry of Health doesn't make this data accessible enough. It is hard to find, tables have datestamped names, many images without links to underlying data, some time sync issues. Piss poor. https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation

COVID-19: Current situation

Information on current cases and on measures against COVID-19 in New Zealand.

https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation

5. I fired up #PowerBI and used PowerQuery to get data. While it can be downloaded as XLS, I'm scraping it direct from the page. @minhealthnz add datetime to all table names that unnecessarily makes this harder. Not cool, MOH.

https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation/covid-19-current-cases/covid-19-current-cases-details#probable

6. In some cases I'm using PowerQuery's very cool Add Table Using Examples to get around @minhealthnz's bad table naming. This functionality makes it childs play to scrape from the web, and produces the PowerQuery M code shown below. When the hell will this come to #Excel?

7. Next its time to work out how best to present the data.Cumulative vs daily totals seems like the best starting point. I've seen dashboards that put this on one graph - either at the same time, or via a toggle. Why would you impose that on the user? Just use two graphs.

8. The reason I used a line for Total here is that this is the only way I could work out how to get #PowerBI to show the total in a tooltip. @MSPowerBI there should be an options to display totals (and all series values in bar at once) for stacked columns. https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/32082391-totals-in-stacked-column-charts

9. Maybe this works better as a line chart? What do you think, people?

10. Next stop is to think about how to show regional totals and trends. On the totals front, sticking with the same grey theme here's a couple of options. I like the white bars, but perhaps there's not enough contrast. But moving to dark, some numbers become illegible

11. The way around this is to use two series; one of which has the Show Bar Only option ticked. (It has to be a different measure too...if you use the same exact measure, changes to DataBars appear to be global. @Will_MI77 maybe a bug?) I don't want a column name so I used ".".

12. (I haven't tested if you can use non printing characters as column headers, but I really wish #PowerBI would respect my choice of renaming columns as " " or " " or even " X ".

I tried to add an idea for this, but I can't lodge ideas any more. https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/40150345-let-me-use-space-empty-string-in-column-headers

13. So this is where I'm at now. I'll add more as I progress further.

14. Here's what I plan to add:
*Comparison to Australia
*Ethnicity/Age breakdown, weighted by demographic popn
*Map of incidence by DHB with spikes (probably thin bars) rather than bubbles.
*Small multiples allowing comparison between regional trends
* Whatever else U want to see

15. #Covid19 incidence data from @minhealthnz uses unequal age-groups, with 4 bands covering folk aged 1 to 20, but just 1 band for 70+. In a pandemic that disproportionately impacts older folk.

Please give us better access to better data @minhealthnz

https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation/covid-19-current-cases/covid-19-current-cases-details

16. It's also hard to build a dashboard when the publicly available datasources are at different levels of granularity. @minhealthnz give us daily confirmed cases by Dist. Health Board, but only give us hospitalised/recovered/deaths on a national basis.

https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation/covid-19-current-cases

17. Jeez: the web tables on the MoH website is for April only, and the only way to get all the data is to download an Excel file. Which is hard to automate because @minhealthnz hardcode the date in the filename like so:
case-list-12-april-2020-for-web.xlsx

Another fail. C'mon!

18. Hmm. The table on the MOH site here has NZ population at 4.9 Million. I thought we were 4.6 million.

I've added in NZ Census figures on the right that show 15% variation for Auckland. This affects per capital testing figures. What am I missing?

https://www.health.govt.nz/our-work/diseases-and-conditions/covid-19-novel-coronavirus/covid-19-current-situation/covid-19-current-cases/covid-19-testing-region

Latest Threads Unrolled: