5

I want to create a calculated column that is equal to the percent of the total of the previous column, but only for the rows that are selected.

For example, the two columns below show rows where I've filtered for only the rows of interest. The sum is accurate, but I want the percent of the total to be only out of sum of the currently selected rows rather than the absolute total (which is how it's currently being calculated). I want the percent of the total to dynamically change depending on what is filtered in the data table. Is this possible?

Image of my 2 columns:

enter image description here

I know spotfire somehow calculates this becuase when I insert a bar graph using % of Total(SumofComponents) the ratio is only out of the current total.

Image of my bar chart: enter image description here

niko
  • 3,946
  • 12
  • 26
avgara
  • 51
  • 1
  • 2
  • Is SumOfComponets a calculated column? Can you elaborate on what it is? Share your data structure? – S3S Jun 22 '16 at 13:39
  • after rereading your question and my response I think you might be trying to do something completely different. can you explain specifically why you need the filter to affect your calculated column? what is your end goal? – niko Jun 22 '16 at 16:53
  • I want to use the calculated column for a subsequent calculation, which is why I need it in the data table rather than in a visualization. I was able to find a solution using an R script where a duplicate table was generated, but was updated when the original table was filtered. – avgara Jun 23 '16 at 12:32

1 Answers1

3

much like in programming, Spotfire has a rough concept of scope. and unfortunately, calculated columns are above filters in terms of scope; they have no concept of what is and is not filtered.

visualizations themselves, however, do*!

what you can do in this case is to put your expression on the Y axis (it looks like you've done this), and it will respect your selected filters*.

*caveats: there are a few ways that filtering can be negated on a given visualization:

  1. Properties>>Appearance>>Show shadows indicating the unfiltered data. this option shows a grey bar that represents the data hidden by whatever filter selection was made

  2. Properties>>Data>>Limit data using filterings. these options allow the viz to use separate filtering sets ("Filter Schemes" as they're called in Spotfire) or none at all (to ignore filters completely)

  3. Properties>>Subsets. by default there are three subsets: "All Data" which ignores filters, "Not in current filtering" which inverts the filter selection (e.g., if you filter a boolean column to show only TRUE, this chart would show only FALSE), and "Current filtering" which is the default behavior. you can check the online help for additional subsets that you can add.

niko
  • 3,946
  • 12
  • 26