Sunday, June 7, 2015

Histogram via LOD

I am learning a bit more Level of Details(LOD) in Tableau 9 every day. My question has been what it does more to our calculations and analysis? It seem we can do a lot without LOD. Maybe it's just the convenience of using Fixed/Include/Exclude to create aggregations? The formulas becomes shorter and more compact?

My biggest question remains: what can LOD do that we can't do without LOD? What makes LOD absolutely necessary? Anything LOD can do 10x or 100x better than what we have got?

Oh LOD, you see, I have had quite a few doubts and questions.

Simply put, it took a bit of time for me to appreciate the power of LOD.

Recently I got some answers. I am going to use my favorite topic "Histogram" to present my findings.

Histogram is a simple and popular analysis method. Yet its calculation has never been straightforward. If LOD can make histogram's calculation better or easier or clearer, then it will be beneficial to a lot of people. Otherwise LOD may not be worth it. If it is only applicable to some exotic calculations, it is not that useful either.

Histogram via LOD

I will use the same data set as in
Histograms on Aggregated Measures without Table Calculations

Step 1. Create a calc field [Item Count via LOD] and drag it to Columns and turn it into Discrete:
  • {FIXED [Customer Name]:SUM([Number of Records])}
Step 2. Right click [Customer Name] and drag it to Rows. Select Count Distinct at popup.
Step 3. Drag [Item Count via LOD] to the Label shelf.

And it's done. This is quick, isn't it?

Not so quick to the conclusion yet. In this method,
Histograms on Aggregated Measures without Table Calculations
We don't need to create any calculated field. We can do it all by mouse.

In this method via LOD, we have to do some coding to create a calculated field. I don't see the benefit of using LOD here.

Tableau 9 natively supports creating uniform bins on LOD aggregations. But we can do it without LOD, albeit a bit tedious. I don't think it's a big advantage. It's a convenience at most.

So what's the big deal about LOD?

The big deal runs deeper than I thought. Here I am going to use a couple of examples to illustrate.

Example 1. Drill-Down

We have multiple ways of creating histograms using Tableau before LOD. But none of the histograms thus created, can facilitate drill down. That is, we are not able to view the underlying data in each bar.

Now with LOD, we can do the drill down. In this example, we need to find out the number of customers per the number of purchased items.

To illustrate, let's create a details worksheet with a few columns. Then create a dashboard and create an action for histogram bars to filter the details.
The workbook can be downloaded here.

Example 2. Stacking Multiple Histograms 

...over the same bins of course.

Recently there is a post in Tableau forum asking to tally up the occurrences of numbers (1-59) in multiple PowerBall lottery drawings, over different dates from 2004 to 2015. In each drawing, there are 5 numbers drawn out of 1 to 59.

The requirement means to create histograms (occurrences of the 59 numbers) for the first drawn number, the second drawn number up to the fifth drawn number. So there are 5 histograms to be created.

Creating those histograms individually is not a problem.
The problem is to let them use the same bins or the same dimension of 1-59. Creating data relationship between [First number] and [Second number] is not enough. The required table calculations and filters on the secondary data sources just don't work.

Here LOD comes to the rescue! By creating a calculated field [Histo 1]:
  • {FIXED [First number]: COUNTD([Draw Date])}
This formula lets us create histograms for each of the 5 numbers, without table calculations and filters! Now we can build relationships between [First number] and [Second number] etc. In addition, we need to duplicate the data sources 4 times a priori.
Then we can have all 5 histograms share the same dimension [First number]. And we can stack them in the same table and the same line chart. Without LOD, I don't know how to accomplish this. (Maybe it's just me. Let me know if you can do this sans LOD.)
The workbook for this example can be found here.

Conclusion

LOD seems to have opened a new horizon in data analysis and representation. It does give us a much more powerful tool in data exploration. Now we can do something that we couldn't do before, as illustrated by 2 examples in this blog. Examples are as simple and fundamental as creating histograms. 

Can't believe this is my 6th article on histogram!

Thanks, LOD!

My Histogram Series




1 comment:

  1. Alex, you are awesome. thank you for doing this blog and helping out us novices :)

    ReplyDelete