I’ve been messing around with Excel; trying to create a “punchcard chart” that will let me visualise data by hour and day at the same time. This is where I’ve got to so far, using nothing more than COUNTIFS and SUMIFS.

It works, more or less, although there are some bits to tidy up; notably that y-axis, which should really read Sunday through Saturday or the like.

Knowing that Google Sheets/Spreadsheets permits the QUERY formula which allows SQL like data manipulation I thought I’d give it a try. The result is mixed. As thought reshaping the data was relatively straight forward using a query along the lines of:

For reference: I’ve had problems embedding interactive Google charts in blog posts. On self-hosted WordPress sites the data source url gets mangled by the editor. My current fix is to use the Raw HTML plugin which preserves the url. Worth also considering that interactive charts may not appear in RSS feed readers. You can try to get around this with the noscript tag (see this gist for example) [Also Cloudflare users need to note this]

Looking at the bubble chart option it says:

The first column in the table should be text, and represents the label of that bubble. The numbers in the second column are plotted on the x axis. The numbers in the third column are plotted on the y axis. The optional fourth column should be text, and determines the bubble colour. The optional fifth column is numeric, and determines the size of the bubble.

Okay … so in the first image you’ll see I’ve had to start spoofing columns. In B:B I’ve had to create a column of unique labels. In E:E I needed to create a dataset for results where the count is zero. This is because the bubble chart will render bubbles even if the size is zero. To get around this I’ve tried removed the fill on zero values. Another aspect I’m not entirely convinced with is the bubble scaling, but I’ll let you decide.

Disclaimer

The views I express here are mine alone and do not necessarily reflect the views of my employer or any other party.

All code, applications and templates on this site are distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.