Like I said in one of my first tinkering blogs, a lot of what I learn is from tinkering around with some question I find on the Tableau Forums. I found this question today: **I want to get count based on number of counts**. To start with Ashish wanted to figure out how many medical tests each patient had been given. But as with many forum questions, it turned out to be a bit more complicated than that. What she really wanted to figure out was what percent of patients are given 1,2,3,4… tests as compared to the whole. So the assignment is:

- Count number of tests for each patient
- Bin the patients by total number of tests they’ve been given
- Calculate the percent of the total each bin contributes to the whole

In 8.2 something like this is more work than it’s worth (and there’s always that extra dimension that gets mentioned at the very end that mucks everything up). But with the new LOD Expression this becomes quite simple, and even a bit enjoyable. So let’s work through it.

First I want a count of the number of tests per patient. Since I only want the Patient ID to be considered when making the count, I know I’m going to use a FIXED LOD expression. So I created this calc:

{FIXED [Patient ID] : COUNTD([Test LIS Code])}

Now here’s the new bit I learned today: FIXED (and only FIXED) LOD Expressions can be binned! That’s right, we can now bin an aggregated dimension. So we can first aggregate (count) the LIS Codes, and then we can bin them into a bin size of our choice. How cool is that? How did I learn this? As with so many things, I right-clicked a field and looked to see what Tableau was going to let me do. And there was the Create Bins… option (well the Bins… option in the new Create flyout menu).

Just to finish up, after putting the bins out onto the Column shelf (and adding a parameter to control the size) I just added a quick table calc for percent of total to the LOD calc. Anyway the workbook is posted **here**.

I hope you’ll enjoy your own tinkering with binned LOD expressions.

### Like this:

Like Loading...

*Related*

Hi Shawn,

This is great work. Do you think dynamic bin creation is now possible – i.e. looking at max/min, and creating bin sizes depending on the range/scope?

Typically I do this in SQL by pre-processing, but it would be great if we could do this in AQL CF’s

LikeLike

Allan thanks. Not that I see, at least not using Tableau’s UI. When creating Bins we have a choice to hard code a number, or use a parameter. As you know parameters are not (yet) dynamic. The min/max in the bin dialog box is only useful as a guide to picking a number. It doesn’t have any impact on the bin size. (If that’s what you were talking about.)

Thanks for the comment. I think you’re only the third person who has left one; but then I’m a newbie blogger, and this is a fairly young blog. Cheers.

LikeLike