Counting things that aren't there

So recently we came across a particularly tricky problem and one that visual analytics is always going to find tricky, how do we visualize things that don't exist. Now Andy Kirk has recently done an excellent webinar on this topic, unfortunately, it came along too late for us! So we thought we’d share how we approached this particular problem in Tableau.

Let's start with an overview of the problem, the dataset is a series of entries for a particular set of locations. These entries can belong to one of two (or More) categories.

 

LOCATION_ID

TYPE

Value

A

T

17

B

T

143

B

F

131

C

F

112

D

T

154

D

F

39

E

T

43

F

T

124

F

F

119

G

F

188

H

T

50

H

F

9

I

T

89

J

T

14

J

F

108

K

F

113



All locations should have entries in both Type T and Type F, We want to identify which locations are missing entries and for which type.

 

The first thing we did was create a method for counting the number of records that exist in a category, which is a pretty simple prospect.

 

 

The next step was to isolate the counting of records so that we could count for each location if there were missing records, this was done through a level of detail calculation that looked at the calculation mentioned above. If there were records here then we returned 0 else if there were no records we returned 1.

 

 

This then allows us not only to count the number of locations missing records in a particular category but also now isolate a list of locations missing records.

 

 

Eagle eyed readers may spot the fact that we are not going to pick up Locations that have no records across all categories. This is where some data manipulation may be needed, so if we go back to the data source, we will need a full list of locations, if we have this list we can use the nature of joins to create a better data source.

 

 

Now we are guaranteed to have at least one line for each LocationID because we are doing a full outer join of the data source onto the list of locations. We will end up with some duplication of rows in the locations list but because we are using Tableau, and we have level of detail calculations we don't need to be too concerned about this.

 

 

So that's how we approached this problem, and it goes to show visualizing missing information is possible in tableau under the right circumstances.

 

Please ask questions and leave comments below, we always value your feedback!

Free Alteryx Trial!

Free Tableau Trial!

Get in touch with us today!

Contact us
Cookies make it easier for us to provide you with our services. With the usage of our services you permit us to use cookies.
More information Ok