This weekend I went back to some previous makeover Monday data to have a play with on a train journey. I created a fairly simple viz to show the proportion of seats held by women in the House of Representatives over time, split by party affiliation.

I wanted to include some relevant information in the tooltips, such as how many were held by men, how many by women from each party and the change from the previous year. I also wanted the wording to sound fluent, such as “women held x number of seats, up/down y from the previous year” and for this I needed to make two changes: firstly I wanted the “up” or “down” to be dependent on the value of the change from previous year and I wanted to get rid of the minus sign in front of the negative values.

Firstly I built the simple part of my tooltip, namely the number of seats held by women, and the number held by women in each party.

I also created a calculated field to show how many were held by men. Not knowing a huge amount about US government structure, have made the assumption that this data is for the House of Representatives, which has 435 seats, so I calculated the number of male seats by subtracting the number of female seats from 435.

For the section on change from previous year, I dragged the pill for women total onto the tooltip shelf and used a quick table calculation to give me change from previous year. I then dragged this back onto the measures pane to create a new calculated field for it and named it “change from previous year”. I find this easier to do than writing out the constituent parts of the quick table calc but if you find that easier you can write it out.

Next, I created a new calculated field to give me the phrasing “up”, “down” or “no change” depending on whether “change from previous year” was greater than, less than or equal to zero, respectively. I called this calculated field “up or down from previous year.

Finally, I created a calculated field to return the numbering, which I called “numbering”. This was built similarly to the previous, based on whether “change from previous year” was greater than, equal to, or less than zero and returning the value when it was greater than or less than but returning an empty text field if it was equal to zero; the phrase “no change 0 from previous year” doesn’t make grammatical sense and is what would be in the tooltip without this extra calculated field. The tricky part was that I had to convert the returned numerical value into a string, because a calculated field can’t return a mix of integer and string values. To remove the minus sign, I asked the calc to return me the inverse value if it was less than zero.

I then dragged these two new calcs onto the tooltip shelf and built out the phrasing.

As a side note, this can all be done in a single calculated field, but this can become quite complicated if unfamiliar with calcs so I split it into its constituent paths, but if you’re interested it could look something like this:

the +’s add the parts of the calc together like links in a chain and the ” “s are in there to provide the right spacing between the constituent parts.