To this post I've attached a packaged workbook with staged data so that you can follow along with my problem description...
I'm in the process of performing a Recency-Frequency-Monetary (RFM) analysis and would like to create dual-axis histograms for each variable showing frequency counts per bin on the right axis and the cumulative percent of total on the left axis. I've created a parameter (called Purchase Attribute) that allows the user to dynamically select one of the three RFM variables, which links to a calculated field (called Select Purchase Attribute) that allows for the parameter control to dictate which variable is plotted.
Instead of using the default binning algorithms in Tableau, I'd like to use the Freedman-Diaconis rule for determining bin width. This rule asserts that the bin width for a continuous variable is equal to [2 * (IQR/n^(1/3))] where IQR is the interquartile range and n is the number of records. I've implement this equation with a calculated field (called FD Bin Size) which manually calculates the IQR and divides by the cube root of the number of records (count of ID field):
2 * ((PERCENTILE([Select Purchase Attribute], 0.75) - PERCENTILE([Select Purchase Attribute], 0.25)) / POWER(COUNT([ID]), 1/3))
My strategy here was to link the Select Purchase Attribute calculated field to the FD Bin Size calculated field so that the bin-width would be automatically calculated and used for plotting once the user chose a variable for viewing. Then (I thought) I'd be able to edit the Select Purchase Attribute (bin) dimension so that the bin size used for plotting was determined by the FD Bin Size calculated value...which is where my dilemma resides. I've verified with my work in R that the FD Bin Size calculation in Tableau is correct, but I've not found a way to link this dynamic calculation to the Select Purchase Attribute (bin) field.
Any Tableau Jedis out there who can help show me the light? It would be much appreciated!
Here's some more info on the parameters and calculated fields:
RFM_Hist.twbx 1.7 MB