The Filtered Rows option for selectors has provided a very valuable tool for XCelsius designers and is probably used in 99% of dashboards created with this tool. It essentially allows us to select unknown multiple of out of order rows based upon a value in the rows. There are times, however, when a filtered column option would be just as useful but, alas, it is not a built in option.
Using the Source Data Component, however, we can get the same advantages of an unknown multiple of out of order columns based upon a value in the columns. I like to call it, “Filtered Columns.”
The Source Data Component is a lesser known component. Its job is to select rows or, in this case, columns, based upon an index of the column in an array. More simply put, it will select the first column when the index is 0 and put it in its designated destination, or if the index is 1 it selects the second column for the destination and so forth.
Out of Order
For filtered columns we are accomplishing the out of order part by selecting our columns based upon an index that is calculated using Match(). So let’s first look at our array and set up our indexing.
In our array, we are looking first for all the columns for Trains. In order to use Match(), we need to differentiate each of these columns. This is done using a COUNTIF() formula that will return a 1 for the first, a 2 for the second and so on.
This is then concatenated with the cell with the value (i.e. the cell containing “Train).
So Now we have a result that is unique for each column and my spreadsheet looks like this:
which can be captured by Match(). The -1 is necessary to make it a match that returns the 0 as the first column indexing expected for the source data component.
Instead of “Trains1” I use a concatenation formula with the labels used in my selector. For instance, I concatenate my first label, Train with a cell containing 1 and then I don’t have to type anything and it all becomes a formula.
So I have my labels set up as a regular column type selection. This part could also be set up as rows. This is just a normal selector at this point. In mine I select a label for a column and move that column into a destination column. This selector destination column is what will be picked up by the source data components.
I don’t particularly like the #NA and it doesn’t work well with the source data component. So we might get rid of it with this formula first….
But in fact we don’t want a null here, we want an index value for our source data component that works like a null. For that we want a -1. A -1 will be effectively ignored by a source data component. In this way we can have a few or none of one value and a whole bunch of columns of another value. Or, what I call Unknown Multiple.
You’ll notice, too, that I added a couple of possible labels in Y and Z because I don’t necessarily know which values I’ll have in my array as my labels. So I plan on a maximum number of values (8 in this case) and set up a formula to capture my labels:
This formula returns the Train value in S6 but a null in Y6. And, of course, I set up my selector to ignore blanks.
You’ll also notice that I set up two additional indexing rows more than I needed (rows 11 and 12) because I don’t know how many of each value I’ll have. In this case, I just need to know the maximum number of any one value for my set up.
Using the Source Data Component
One Source Data Component is then created for each of the possible result columns up to the maximum. In my example, I need 6 source data components. When “Train” is selected, one will capture the column in 0 index (my first column), one will capture the column in 5 index (4th column), one will capture the column in 11 index (10th column) and so forth. Since my last two rows are superfluous, I only really needed 4 source data components, but I planned conservatively for a maximum.
The Source Data Component is found in the Other category.
It will not appear anywhere on your dashboard because it just does its thing behind the scenes.
The first one I have is set up like to insert a column from my array (Source Data) into the first column of a second array that has 6 columns (my conservatively planned maximum).
It knows which column of my array to put there because it is pointing to a cell containing the index value, in this case, 0. This means it selects the first column in my array.
This isn’t as elegant as the filtered rows and it is slightly deficient in comparison to that method. It involves a few more steps and calculations. And I had to plan for a maximum number of labels (i.e. planning for labels that would have been in Y6 or Z6) and for a maximum number of any one of the values (i.e. planning for 6 columns containing Trains).
So this isn’t at all a substitute for the built in option. I use Filtered Rows over Filtered Columns whenever possible. But for those times when I need it, this works well and is performant.