Quantified Office: Part 2 – The Sunrise Chart


Quantified Office: Part 2 – The Sunrise Chart

Quantified Office is a series in which we tackle interesting methods of data collection, analysis and visualization to quantify activities at InterWorks' offices.

In Part 1 of this is series, I explained the general premise behind our Quantified Office dashboard. In this part of the series, I’ll explain why we chose a visualization type called the “sunrise chart” and show how we created it. I’m the first to admit that the sunrise chart in our dashboard doesn’t give us very valuable information. We wrapped bars around a semi-circle – you definitely can’t get any information out of it easily.

That being said, it isn’t particularly meant for information. It does happen to contain the daily start/end times, paired with sunrise/sunset data. It is, however, a fairly pretty art project. More importantly, it is a great example of what you can do in Tableau if you set your mind to it! Besides, it pairs very well with the data, as we are indeed monitoring sunrise/set times.

Best practices aside, you may have a good use for a similar chart. The idea for this chart is born from a blog post written by Dave Hart, an InterWorks consultant out of the UK. Dave wrote an interesting piece on a Radial Bar Chart. Adapting Dave’s method, I created the semi-circle used in the dashboard below. I will show you how I created this and you can decide on the ethics of using it in your dashboards.

The Math Theory

Thinking back to trigonometry classes in high school, you probably vaguely remember the concepts of sine and cosine. If you did much graphing of them, you will remember they create semi-circle arcs that complement each other on a graph.

We can graph this using the calculation below for our radians, where x is a point on our line and t is the total number of objects:


Or, in Tableau:

([x]-1) * (1/{MAX([x])} * 2 * PI()))

These functions, while fairly arbitrary in school, have amazing uses in the real world. In our example, we can graph sine against cosign on a simple X/Y axis and we get a circle!

While this example gets us a full circle, what we are really going for in our graph is a semi-circle. Our original query utilized the full sine and cosine wave, but for a semi-circle we only need half of it. Luckily, a quick modification to our calculation allows us to use only half of the circle:


Or, in Tableau:

([x]-1) * (1/{MAX([x])} * PI()))

How It’s Done

Now that we’ve covered how to map out a semi-circle in Tableau on an X/Y plane, we can start to build out our graph. In this particular use case, I wanted lines to serve as the “rays” on the sun. To do this in Tableau, we will want to use path mapping.

Basically, we want our data to be duplicated with two points – one at the beginning of the line and one at the end. Using some math, we can find a new X/Y coordinate for the secondary point location. The third point is our sunrise time. Go find the sunrise data for your dates and use it for the third point.

But before we get too far, let’s do some data preparation. This isn’t necessary – we can do all of these pieces in Tableau – but greatly simplifies the Tableau processes involved.
First, I converted our timestamps to seconds. This can be achieved by a simple formula:

(Hours * 3600) + (Minutes * 60)

For our Tableau calculations, we will also want a couple of auxiliary fields. These can be calculated in Tableau, but to simplify the math I just point them in Excel. In the attached Sunset.xlsx file, you will see these fields. They include the following:

  • Index – This is a simple INDEX() type field that applies to a subset of our data, restarting for each Path. An index of 2 will match up with the other three path points at Index 2.
  • PathOrder – Which dot on the path is this record? If 3, then this is sunrise data. Otherwise, it is one of the duplicated data points.
  • Max Daily Entry – Maximum timestamp in our data. This is used for calculating the size of our line.
  • Min Daily Entry – Minimum timestamp in our data. This is used for calculating the size of our line.

Now that we have our data formatted, let’s move over to Tableau!

We will need a small handful of calculations in Tableau to determine where to map our points.

  • Normalized Length – Calculates the length of our bar. If we are at our original point, this simply returns our starting point: .4. Otherwise, it determines where our point should go. (The .4 is to create the inside whitespace. You can reduce this to make the whitespace smaller or larger as desired.)
    + IIF(
        [Path Order] = 0,
        (INT([Daily Entry])-([Min Daily Entry]/2))/([Max Daily Entry]-([Min Daily Entry]/2)) * (1-.4)
  • Radial Angel – This is the radian we pass into SIN() or COS(). It more or less cuts up the semi-circle into pieces, depending on how many data points you have.
    ([Index]-1) * (1/([Total]-1)) * PI()
  • Radial X – Our X axis. Cosine is used for this calculation.
    [Normalized Length] * COS([Radial Angle])
  • Radial Y – Our Y axis. Sine is used for this calculation.
    [Normalized Length] * SIN([Radial Angle])
  • Is Sunrise/Sunset.
    [Path Order]=2

Now that we have all the pieces, we simply need to assemble them! Select Line in the Marks area of a new chart. Then, simply drag Radial X to Columns, Radial Y to Rows, Is Sunset/Sunrise to Size and Path Order to Path. Make sure Radial X and Radial Y are continuous (green). If they are not, then right-click on the pills and select continuous to change them.

And there you have it – a sunrise chart! I’ve attached all of the pieces, so feel free to dig into them if you wish to dissect the chart more. Make sure to keep an eye out for Part 3 of our Quantified Office series, where we share how to obtain data from the motion sensor.

More About the Author

Derrick Austin

Curator Platform Lead
Five Years of Custom Embedded Analytics with Curator Thinking back, 2013 was an interesting year for many reasons. During the “International Year of Quinoa”, the world welcomed Prince ...
Curator by InterWorks Officially Turns 4 Curator is turning four years old! It’s awesome to be celebrating this milestone, but some of you may have just met Curator for the ...

See more from this author →

InterWorks uses cookies to allow us to better understand how the site is used. By continuing to use this site, you consent to this policy. Review Policy OK


Interworks GmbH
Ratinger Straße 9
40213 Düsseldorf
Geschäftsführer: Mel Stephenson

Kontaktaufnahme: markus@interworks.eu
Telefon: +49 (0)211 5408 5301

Amtsgericht Düsseldorf HRB 79752
UstldNr: DE 313 353 072


Love our blog? You should see our emails. Sign up for our newsletter!