Some of the more interesting and practical sessions at TC14 were the Rapid Fire Tips and Tricks sessions led by Daniel Hom. Don't worry if you missed them: we're bringing two of the best tricks shown in those sessions straight to you today.

Automatic Switching from a Fixed View to a Scrolling View

Displaying data can often be a challenge. One particularly annoying problem viz authors often run into is choosing whether to display a viz with the "Fixed Width/Height" or "Entire View".

"Entire View" is a great way to neatly fill all of your available space, but once you get above a certain number of rows the view begins to scrunch together and the row headers become unreadable, as you see below.

But, what if it were possible to use BOTH methods in the same viz; to switch between "Entire View" and "Fixed Width". As you might have guessed by now, there is a way.

This method utilizes the ability of layout containers on dashboards to hide vizes. To start, you simply create a vertical layout container by dragging one out from the left hand side of your dashboard. Then, copy the worksheet you want to display, and then embed BOTH the original and the copy of the worksheet on top of one another in the vertical layout container. Switch one of the views to display "Entire View" and the other to display "Fit Width", it doesn't matter which one you use since they should be identical. For this to work seamlessly, you'll also want to delete the sheet titles on the dashboard.

Now all we have to do is display a single view at a time. To do this we can use the "size()" calculation. This calculation is very simple: it finds the number of rows in a view. So, if size()<21 (if there are less than 21 rows in the view), then we want to filter out all of the data in the "Fit Width" worksheet (the one with the scrollbar). Conversely, if there are more than 20 rows in the view (the calculated field for that is size()>=21), we only want to show the "Fit Width" worksheet. You can easily achieve this by creating the two calculations above and then dragging them onto the filters shelf in their respective sheets.

The result? A dashboard that displays data in the most efficient method, regardless of how many rows you have filtered. Click the filter below to add a row and see this in action. The only change you should see is the addition of a row and a scrollbar. Subtle, yet useful.

Thanks to Ryan Janoff for this tip.

Bar Chart Tooltips

A frequent feature request that we receive is for the ability to embed charts within tooltips. Although this isn't possible, there is a workaround that can enable you to create bar charts within tooltips.

This trick is actually much simpler than you might imagine. It utilizes a special character that you can access by holding down Alt, then typing 219 on your number pad. █, there it is! If you have an interest, you can see that this is actually just one of an enormous number of alt codes that you can utilize to create other special characters. The ★, for instance might be good for a review or ratings viz. But I digress...

To create a bar chart, all you need to do is string together a series of these █ characters in a calculated field. Since there is no character spacing (or "kerning"), a series of them strung together looks like a solid bar ████████. Of course, you can easily control the length of that bar in a calculated field, like the one below.

LEFT("███████████████████████████████",ROUND([% Economic ]))

I shortened the field so it would fit there more compactly, but here's how it works. You have 100 █ characters strung together, and then a field that has a value 1-100 (a percentage, for instance). Then, you use the "Left" calculation to display only the value of that field. If the field's value is 34.6, the calculation above would round it to 35 and display 35 █ characters. With two or more of these calculated fields (one for each measure you want to display) on top of one another, you have a proportional bar chart. Hover over a dot below to see what I mean, and download this viz to learn more about how it is made. Keep in mind, I simplified the calculation from what is in the workbook, but the concept outlined above is all you need to know to get started.

Thanks to Andy Cotgreave and Andrew Cheung for this tip.

Keep in mind, these are just two tricks out of the 15 that were shown in the Rapid Fire Tips and Tricks sessions at the conference. Sign up for TC15 now to make sure you don't miss out next year!

You might also be interested in...


should the first 2 images be broken? they are for me in chrome

I am using Chrome - only (minor) issue is the bars in the tooltip have some vertical white stripes in them. Download the view and the stripes go away using Reader or Desktop.
I had no issue with the first view.

This is great stuff. It's remarkable how duplication can be consistently be used as creative solution, from duplicating fields, worksheets and even data sources. Thanks for sharing!

Hi there , the first 2 images are broken, please can you repare that?

Very nice stuff.

You can also create colored bars in the tooltips. Just format the text color of the calculated field name you insert in the tooltip.

Awesome! Quick question on the Tooltip barcharts...I am trying to replicate, but when I create the Economic bar chart calculated field, it is a Dimension. When I convert it to measure, the default aggregation is CountD. But on your calculated field the Default Aggregation is set to Custom.
FYI - I can get the bars to appear with my calculated field if I change CountD to Max or Min

Automatic Switching from a Fixed View to a Scrolling View works fine till the moment I check All and then unchecked it to have None selected. Then whatever I select it shows scrollable version.

Please disregard my message. It works fine

Add new comment

Subscribe to our blog