We have all come across this issue before…
You just built a kick-ass dashboard and then you start adding some interactive filters and all of sudden you are left with a half empty screen. And then the realization kicks in… you have sparse data and not ever dimension has activity. AND then someone says, “why don’t you just have it say no data available when there is no data available”?
Well, wouldn’t that be nice? But, how do you write a calc when there is no data? You don’t have to!
I came across this issue recently on a KPI dashboard that had maybe 15 sheets on it. The dashboard had several very small charts in a banner across the top (i.e., total dollars, a performance measure, MoM change, YoY change, and a sparkline) and three main charts. Some of my filters completely wiped out the dashboard.
I wanted to be able to show an indicator like ‘No data available’ for any chart that had no data. Then, a simple hack came to me one night as I was thinking about this problem and walking through my kitchen.
I have no idea why I remember being in the kitchen…
My idea was to tile worksheets inside of containers, float the containers on a dashboard, and then float a text box behind the container that simply said ‘No data available’. This idea takes advantage of the layering that is possible with floating and the fact that worksheets are not transparent.
Why containers? Two reasons actually. The first reason is because you can collapse a sheet within a container. The second is because containers are transparent if you don’t apply shading.
Here is an example…click on North Dakota. You will see an indication there is no data available.
It is pretty easy to setup.
- Go ahead and tile your dashboard the way you want it. Then simply make the top container floating. From the Layout tab on the dashboard, right-click on the top container and select floating. You may need to reposition the X/Y/W/H coordinates.
- If you have multiple sheets in a container that are nulling out, you may need to add some subcontainers. In the example above, there is a container around the sparkline and another container around the sub-category chart. I set a fixed height on these sub-containers so both worksheets don’t collapse to the top of the dashboard. Instead they will collapse within these sub-containers.
- Then, Simply float a text box over the sheets, add your text and then right-click->Floating Order->Send to Back
It works with pop-out menus too.
Using Robert Rouse’s collapsing menu for all the controls (i.e., filters, etc.), you can set this up. The trick is to make one copy of the blank toggle sheet. Then mirror the container setup, but instead of sheets, add a bunch of text boxes that say no data. Lastly, change the float order of the mirrored container setup and to Send to Back.
Here is an example. Click on North Dakota and open and close the menu.
I hope this was useful. Thanks for reading!