4/22/2020
Knowledge Densification Utilizing Area Completion and Area Padding
One of many extra sophisticated matters in Tableau is Knowledge Densification. I feel the ideas are rather more troublesome than the precise implementation in Tableau, as a result of in lots of circumstances it is rather straightforward to activate information densification. As with many issues in Tableau, we construct on the works of others, so earlier than I begin I need to acknowledge two individuals who pioneered these methods early on and have been instrumental to me in my Tableau studying. The OZM (Authentic Zen Masters from the very top notch of Tableau Zen Masters) Joe Mako and Jonathan Drummey. I’ve realized a lot about Tableau from these two guys so a giant thanks to each of them.
First, let’s begin with a couple of definitions. These come from Joe and are mentioned in depth in this video on Data Densification here.
Definitions
Knowledge Densification – Umbrella time period that encompasses all conditions the place Tableau provides Marks for Dimension worth mixtures.
Area Completion – Marks are added for potential Dimension worth mixtures
Area Padding – Marks are added for potential values within the vary of a Vary Conscious capsule
Sparse Knowledge – Knowledge the place not all potential mixture of Dimension values exist within the information returned from the info supply.
Vary Conscious Tablet – A Dimension capsule with a area information sort of both Date, Date Time, or Bin. NOTE – The exception is a Date Time set to Actual Date.
Opposing Cabinets – The Rows and Columns cabinets are Opposing Cabinets
To activate Knowledge Densification:
Area Completion – Initiated with 2 or extra Dimensional capsules
Area Padding – Initiated with Present Lacking Values from a Vary Conscious capsule
Let’s begin with some Sparse Knowledge. Under are two examples of sparse information. The primary is exhibiting classes, however there are measures lacking for a few of them. The second instance is exhibiting a date wtih a worth, however discover that there are dates lacking; we solely have information for 3 non-consecutive days in November 2019. These are examples of sparse information.
Within the first instance beneath, the classes have lacking values, we are able to use Area Completion to fill in these values. To do that, we are able to use a calculation akin to INDEX() to activate Knowledge Densification. Discover the « abc » solely exhibits the place there are values, however after including the INDEX we see « abc » in each cell.
Within the second instance beneath, there are days of the month, DAY(Order Date), on Columns. This can be a Vary Conscious Tablet (A Dimension capsule with a area information sort of both Date, Date Time, or Bin). We are able to use Area Padding to fill in these values. To do that, right-click on the dimension capsule and choose « Present Lacking Values ».
That was straightforward, proper? As I discussed at first of this, I feel the ideas are tougher to understand than the precise execution of those steps. A easy calculation, or one-click of an choice, can activate information densification.
To reveal this method in a extra sensible means, I’m going to make use of an instance by Ken Flerlage that he wrote about in his blog post on Creating a Data Scaffold in Tableau here (By the way, that is one other approach pioneered by Joe Mako). Ken makes use of this method to fill in values. This comes from a Tableau discussion board query the place somebody is trying to fill in 0% when there is no such thing as a information within the spotlight desk.
Word – I encourage everybody to learn Ken’s weblog put up, as a result of, whereas I’m going to current a a lot simpler answer to this particular instance, there are a lot of cases the place a scaffolding approach might be very helpful.
If you want to observe alongside, then listed here are the steps to construct a fast spotlight desk utilizing the Superstore information.
Double-click Order Date so as to add it to Columns.
Proper-Click on on Order Date and alter it to Month(Order Date) by choosing the primary Month within the checklist.
Take away Yr(Order Date) from the Columns (drag it up off the canvas).
Double-click Buyer Title so as to add it to Rows.
Double-click Revenue Ratio so as to add it to Textual content on the Marks Card.
Drag Revenue Ratio to Shade on the Marks Card.
Choose the drop down menu on the Marks Card and choose « Sq. » to alter the view to a spotlight desk.
Drag Order Date to Filters and choose Years to filter solely 2019.
Double-click the colour legend and choose « Orange-Blue-White Divering » from the dropdown menu and click on OK.
It’s best to now have a view that appears just like the spotlight desk beneath. Discover the lacking information on this desk.
This can be a nice instance as a result of it has two opposing dimensions on the Columns and Rows and one among them is a variety conscious capsule. That implies that we are able to use both Area Completion or Area Padding to fill within the lacking values on this spotlight desk, with out utilizing Date Scaffolding. Bear in mind, the purpose is to fill in « 0% » for all of the empty values the place there is no such thing as a information.
Let’s begin with area padding, which we are able to do in two easy steps.
First, right-click on the Vary Conscious Tablet, on this case that’s the MONTH(Order Date), and choose « Present Lacking Values ». This has turned on information densification. In the event you did not change the colour to « Orange-Blue-White Diverging » then you definately would see that the spotlight desk has crammed in shade in all the empty squares. Additionally, in the event you take away all of the capsules from the Marks Card and set the Mark Sort within the dropdown field to « Textual content », you’ll discover that Tableau is filling in « abc » for all the empty marks much like the instance above). There’s yet one more easy step, which is to assign a worth for the lacking information.
To assign a particular worth, for instance the « 0% » that we need to present up within the spotlight desk, we merely right-click on the measure that’s within the desk (on this case Revenue Ratio on the Marks Card Textual content) and choose Format. Beneath the formatting menu, set the Particular Values (eg NULL) textual content to 0% (or no matter worth or textual content you need to present up within the empty cells, textual content akin to « NONE » or « NULL » will work too). With the colour set to « Orange-Blue-White Diverging » the 0% can be white and the values that aren’t empty will present a worth with shade. It’s best to now have a spotlight desk just like the one beneath.
In two quite simple steps we now have the desk format we had been in search of. Now let’s do the identical factor utilizing Area Completion. You’ll have to undo the steps you simply did to activate Area Padding, so hit the again button twice to undo them.
Fixing with Area Completion can be a two-step course of, however the formatting step is strictly the identical. So the one distinction is learn how to activate Knowledge Densification. To do this, double-click the white space on the backside of the Marks Card. This can mean you can enter an in-line method. Merely sort Index() and hit enter. Alternatively, you could possibly create a brand new calculated area utilizing the INDEX() operate and add that to Particulars on the Marks Card. In both case, this can activate Area Completion.
Now repeat the formatting step, merely right-click on the measure that’s within the desk (Revenue Ratio on the Marks Card Textual content) and choose Format. Beneath the formatting menu, set the Particular Values (eg NULL) textual content to 0% (or no matter worth or textual content you need to present up within the empty cells). It’s best to now have a spotlight desk just like the one beneath, and the identical formatting outcome as earlier than.
As I discussed early, this can be a nice instance as a result of we are able to activate Knowledge Densification with both Area Padding (utilizing the Vary Conscious Tablet) or Area Completion (utilizing the INDEX() operate with the opposing cabinets). Each resolve the issue and create the specified outcome.
Under is a Tableau Public Visualization with the slides and these examples.
