This post about how to create bins from a measure was originally written in the days of Tableau 7. Now things have evolved and it is far more straightforward, the updated article on using LOD calculations to create bins from a measure is here.
For this post I have to give a huge thanks to Richard Leeke who found the ‘Tableau only’ solution. My alternative was to pre-calculate the data outside of Tableau.
Here is a quick overview of what I was trying to do using Tableau. I wanted to create a calculated field and use the output of that field to create bins. The calculated field is a measure, not a dimension, but the same rules apply.
The post is quite long and complex hence it’s broken up into multiple parts. The solution using data blending will be detailed in the next post, creating bins from a calculated field.
The test data has 3 columns: Month, ListingID and EnquiryCount. In other words it showed the enquiry count per listing per month.
Firstly I want to calculate number of enquiries per listing over the entire time period. Then I want to use the result of this calculation for the bins. The number of enquiries for each listing defines which group they belong to – i.e. 1 – 10, 11 – 20, etc.
In other words assume ListingId “1” had an EnquiryCount of 10 in Month 1, 2 enquiries in Month 2 and 8 enquiries in Month 3, Therefore ListingId “1” received 20 enquiries in total so would be in the bin 11-20.
Once I can put each listing in the appropriate bin next I want to see, for each group, what % of total enquiries came in month 1, month 2 and month 3.
Returning to the example, ListingId “1” received 50% of enquiries in Month 1, 10% in Month 2 and 40% in Month 3.
Depending on how the bins are used the bins will recalculate and can return a different result to that expected. This sentence will become clearer later in the post.
Create the calculated fields and bins
The first thing to do is create the bins. Create a calculated field to sum the enquiries:
Use the result of this to create the bins in another calculated field. We will call the bin calculation EnqCountBins, which is below:
This bin field can be used without any problems for 1 dimensional reporting.
We want to know the number of unique ListingIDs within each bin. To find this create another calculated field:
Note, for COUNTD it’s best to use an extract; COUNTD isn’t available for all types of data connection.
Put the EnqCountBins pill to the Columns shelf and the ListingCount pill to the Rows shelf. That gives the listing count. Both pills should display like AGG(CalcField).
You should notice the bins don’t display correctly.
To split the listing counts out into bins drag the ListingID pill on to the Level Of Detail and that completes the chart.
The complications begin when we want to use the result of the bin calculation AND perform further calculations. This will affect the result, the bins calculate as we drag and drop.
As mentioned earlier I’m trying to calculate, for each Enquiry Count bin, within which months they received enquiries. This is where the real complexity begins.
To get the enquiry counts per bucket drag the EnquiryCount pill to Rows shelf in the place of ListingCount. This should be a SUM(EnquiryCount) calculation. Next we want to split the enquiry counts by month. The obvious thing to do is to drag the Month pill into the Colour shelf. Do that and you see things start to go wrong. The bins recalculate dependent on the fields in the view. Now the bin calculation is taking the Month into account.
I have to attribute the solution wholly to Richard Leeke . Data Blending is the answer – which in effect is like a self join in SQL. As things begin to get difficult now I’ve written it up in a separate post.