Search code examples
histogramspotfirebinning

Including only specific rows with AutoBinNumeric() in Spotfire

Hi Spotfire professionals,

I am attempting to make a dynamic set of histograms for a large rectangular data frame (>2M rows) containing 34 unique columns and 69 unique row identifiers which preceed unique test data. The data table is of the following format, where "z" is the test data and a,b,c are unique identifiers:

##   a   b   c   ...   z
1    x1  x2  x3   ...  z1
2    x1  x2  x3   ...  z2 
3    y1  y2  y3   ...  z3
4    y1  y2  y3   ...  z4

What I want to be able to do is filter based on either x1 or y1 and have the histogram of the test data distribution be generated based on that selection. I cannot proceed with current options because different data selections can vary by many orders of magnitude.

I have already attempted BinByEvenDistribution([z],200) and manually forced the x-axis to be constrained to limits that make sense, but what I want is to make a "smarter" version of AutoBinNumeric() that calls on the filtered row value.

Because there are 69 unique row identifiers in my (proprietary) data set, with a variable amount of row IDs for other data sets using this template, I want something that will not require manual user inputs for anything other than selecting a unique row ID and I don't think creating new data tables is the most efficient solution here either.

In summary, can anyone out there provide me with guidance on how to make AutoBinNumeric([z], 50) call out only the z values that correspond with my filtered a,b, or c values without needing to add a ton of calculated columns?

Here is an example of my target output:

Target Output

Here is what I currently get using AutoBinNumeric([z], 5):

AutoBinNumeric()

Solution

  • You can try setting a document property with a data function having the input be an expression of some count of items based on Filtering. Then use that document property in your expression AutoBinNumeric([z], ${DocProperty}) that way as filters are changed your count() or uniquecount() will update then update your binby expression.